Hello All,
I am trying to use the below script to consolidate multiple excel worksheets across multiple workbooks.
I have 10 excel files that contain roughly 20 worksheets (tabs). I am trying to put the data on worksheet 1 (tab 1 called summary) and worksheet 2 (tab 2 called All Breaks) in a new workbook with two tabs one with the consolidated Summary tabs and one with the the consolidated All Breaks tab.
I am trying to use the below script to achieve this. However it keeps failing and returning an error "Object Required" when it gets to the highlighted text (in red) below. I was wondering if anyone knew how to achieve the above?
I am trying to use the below script to consolidate multiple excel worksheets across multiple workbooks.
I have 10 excel files that contain roughly 20 worksheets (tabs). I am trying to put the data on worksheet 1 (tab 1 called summary) and worksheet 2 (tab 2 called All Breaks) in a new workbook with two tabs one with the consolidated Summary tabs and one with the the consolidated All Breaks tab.
I am trying to use the below script to achieve this. However it keeps failing and returning an error "Object Required" when it gets to the highlighted text (in red) below. I was wondering if anyone knew how to achieve the above?
Code:
Sub Copydata()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = "C:\Users\woswald\Documents\Castlelake"
Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)
Dim erow As Long, lastrow As Long, lastcolumn As Long
Do While Filename <> ""
Dim wb As Workbook
Set wb = Workbooks.Open(FolderPath & Filename)
For counter = 1 To 2
'Sheets("Sheet1").Select
wb.Worksheets(counter).Activate
[COLOR=#ff0000]lastrow = ActiveSheet.Cells(Row.Count, "A").End(xlUp).Row[/COLOR]
lastcolumn = ActiveSheet.Cells(A, Column.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
'Sheets("Sheet1").Select
Workbooks("Investran_Colsoildated.xlsm").Worksheets(counter).Activate
erow = ActiveSheet.Cells(Rows, Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActivateSheet.Paste
Next
wb.Close savechanges:=False
Loop
erow = ActivateSheet.Cells(Row, Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Last edited by a moderator:
