Hi,
I've started building a macro that loops the sheets and collects the information onto the first worksheet. I've been using Activesheet and activecell references but i'm afraid looping will change these references. Any help would be great.
Dim row As Integer
Dim ws As Integer
For ws = 1 To Worksheets.Count
For row = 3 To 4
Cells(row, ws + 1).Activate
ActiveCell = WorksheetFunction.VLookup(ActiveCell.Offset(0, -ws), Sheets(ActiveSheet.Index + ws). _
Range("J1:K10"), 2, 0)
Next row
For row = 5 To 10
Cells(row, ws + 1).Activate
ActiveCell = WorksheetFunction.VLookup(ActiveCell.Offset(0, -ws), Sheets(ActiveSheet.Index + ws). _
Range("D1:G1000"), 3, 0)
Next row
For row = 13 To 18
Cells(row, ws + 1).Activate
ActiveCell = WorksheetFunction.VLookup(ActiveCell.Offset(0, -ws), Sheets(ActiveSheet.Index + ws). _
Range("D1:G1000"), 4, 0)
Next row
Cells(29, ws + 1).Activate
Cells(29, ws + 1) = WorksheetFunction.VLookup(ActiveCell.Offset(0, -ws), Sheets(ActiveSheet.Index + ws). _
Range("F1: H1000"), 3, 0)
Next ws
Bookmarks