Copy/combine data from multiple workbooks

[goldbeck09]

Hello,

I have multiple workbooks that I need to combine into one main master workbook. I have a macro already in place that opens/closes all the workbooks, I just need help looping through each open workbook and placing the data into my master sheet. See my progress below so far:

My main spreadsheet is a blank except row 1 is a header.

Sub copydata()

Dim Book As Workbook




For Each Book In Workbooks




Range("A12").Select
'ActiveCell.Offset(RowOffset:=2, columnoffset:=0).Activate
'Range(Selection, Selection.End(xlToLeft)).Select
Rows(ActiveCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


Workbooks("Intl Macro.xlsx").Activate
Sheets("Sheet1").Activate
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Rows(erow)




Next Book






End Sub

 

[goldbeck09]

I am getting an error message runtime 1004 when my code tries to copy the data to the master sheet. Not sure what the fix is.

Code that error is occuring:

ActiveSheet.Paste Destination:=Worksheets("Sheet1").Rows(erow)

Here is the entire code:

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow
Dim Filepath As String


Filepath = "K:\IPR\Cost & Reserve\2015\06 JUN 15\"


MyFile = Dir(Filepath)
Do While Len(MyFile) > 0


If MyFile = "Intl Macro.xlsm" Then


Exit Sub


End If


Workbooks.Open (Filepath & MyFile)
Range("A12").Select
Rows(ActiveCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.CutCopyMode = False
ActiveWorkbook.Close


'Workbooks("Intl Macro.xlsm").Activate
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row




'ActiveSheet.Paste Destination:=Worksheets(“Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
'ActiveSheet.Paste Destination:=Worksheets("Sheet1").Rows(erow)
ActiveSheet.Cells(erow, 1).Paste
'ActiveSheet.Paste
MyFile = Dir
Loop
End Sub