Hi!

New to VBA here and trying to copy workbooks of the same format (but with varying number of rows) into a single workbook. Sheets are the same in the data files and need to be pasted into the corresponding sheets in the aggregated "monthly" file.

So basically I have the following:

Monthly Workbook -- contains macro / button to activate data aggregation

Data files -- multiple files of same format, each with 7 sheets (unique names within workbook) of same names and headers (across workbooks) -- data needs to be appended together



So far I have the following code. It is doing the first part ok - pasting in the first workbooks sheets, but I can't get it to append in the data from the subsequent data files. I am getting a "1004 Error Select Method of Worksheet class failed" on the line I have marked below.


Option Explicit
Sub CopyData()
Dim erow As Long, lastrow As Long, lastcolumn As Long, WbMonthly As Workbook
Dim TargetFiles As FileDialog
Dim FileIdx As Long, DataBook As Workbook
Dim sheet As Worksheet, counter As Long


Set WbMonthly = ThisWorkbook


'prompt user to select files
Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
.AllowMultiSelect = True
.Title = "Multi-select target data files:"
.ButtonName = ""
.Filters.Clear
.Filters.Add ".xlsx files", "*.xlsx"
.Show
End With


For FileIdx = 1 To TargetFiles.SelectedItems.Count
'open the file and assign the workbook/worksheet
Set DataBook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))

'if it is the first data file, copy in all of the sheets including the header row
If FileIdx = 1 Then
For Each sheet In DataBook.Sheets
sheet.Copy After:=WbMonthly.Sheets(WbMonthly.Sheets.Count)
Next sheet
Else

'if it is not the first data file, copy in the data by appending to what is already in the sheet
For counter = 1 To DataBook.Sheets.Count
DataBook.Sheets(counter).Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy

WbMonthly.Sheets(counter + 1).Select 'ERROR
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste
Next


End If

Next




'Close all of the datafiles
For FileIdx = 1 To TargetFiles.SelectedItems.Count
Set DataBook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))
DataBook.Close
Next



End Sub



Any help would be greatly appreciated!! THANK YOU!