folks - I am close using a code from another thread but cant seem to crack the code to copy rows A1:A6 from all the workbooks into the Master sheet. I get the first row only. I need the first 6 rows copied, skip a row, copy the next sheet first six rows, skip a row, etc until all 50 sheets have been copied into the Master. All help is appreciated!
MacroCode:
'Create a workbook for the recap report
Set Master = ThisWorkbook
Do
Workbooks.Open (myPath & "\" & CurrentFileName)
Set sourceBook = Workbooks(CurrentFileName)
Set sourceData = sourceBook.Worksheets(1)
With sourceData
Range("A1:Z" & Range("A" & Rows.Count).End(xlUp).Row).Copy _
Master.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2, 0)
End With
sourceBook.Close
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""
MsgBox "Consolidation complete"
Application.ScreenUpdating = True
End Sub
Bookmarks