I am using a macro to copy data from multiple sheets referenced in a list of cells and place the data it one master sheet.
The formula works and gives me the output i want however every time i run it it finishes with an error of 'Subscript out of range'
Here is the code i'm using
Sub blanktemp()
Dim cell As Range
lastRef = Cells(Rows.Count, 24).End(xlUp).Row
For Each sheetRef In Range("X2:X" & lastRef)
Sheets(sheetRef.Value).Select
lastTest = Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:F" & lastTest).Copy
Sheets("BlankTemplate").Select
blankRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 'use column 2 as at the mo nothing is in column1
Cells(blankRow, 1).Select 'location to paste column 2, next empty row
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next sheetRef
End Sub
Any help to fix the error would be apprciated
Bookmarks