Complicated Excel VBA Macro with Loop

By : Samikon

I require help with a reasonably complicated VBA macro loop for a dataset I've been provided. The dataset exists as one long column one thousands of different entries.

I've tried recording macros but I am at a loss at the best way to approach it. Any help would be greatly appreciated. In its simplest terms, I need to locate a term (ie. "THIS IS A TEST"), copy that cell into new worksheet, go 72 cells up and copy whatever is in that cell into the new worksheet as well.

Logic for the VBA macro Loop...

  1. Scan through all worksheets for the words "THIS IS A TEST"
  2. Copy that cell into a new worksheet (eg. A1)
  3. Go 72 cells up
  4. Copy that cell into the new worksheet (eg. B1)

It needs to loop through the above logic across all open worksheets, dumping the results into a new worksheet.

Once again, thanks for any help I recieve.

By : Samikon


Here is a start. Your notes suggest that the words will only occur once in each sheet and that there will be a cell 72 rows back. I have included notes on checking these two items, but only sketchily.

Dim c As Range
Dim s As Worksheet
Dim sr As Worksheet ''For results
Dim r1 As Long ''Row counter
Dim i As Long ''Incidence counter
Dim firstAddress As Variant

''New worksheet for results
Set sr = ActiveWorkbook.Worksheets.Add
r1 = 1

''It might be better to use a named workbook
For Each s In ActiveWorkbook.Worksheets
    ''Don't check results sheet
    If s.Name 
By : Fionnuala

This video can help you solving your question :)
By: admin