I ran this code from a separate macro (.xlsm) enabled workbook
Try this code
Sub CombineWBks()
Dim strFirstFile, strSecondFile, strThirdFile As String
Dim wbk1, wbk2, wbk3 As Workbook
'Speeding up the macro
Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False
'Change file path to match your files locations
strFirstFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 1.xlsx" 'Change to your location & ensure the workbook exist
strSecondFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 2.xlsx" 'Change to your location & ensure the workbook exist
strThirdFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 3.xlsx" 'Change to your location & ensure the workbook exist
Set wbk1 = Workbooks.Open(strFirstFile)
Set wbk3 = Workbooks.Open(strThirdFile)
'Clears data within sheet1 of the third file
'wbk3.Sheets("sheet1").UsedRange.ClearContents 'Clears the third file's data (used only for testing}
'Copy process begins
wbk1.Sheets("Sheet1").Range("$A:$E").Copy wbk3.Sheets("Sheet1").Range("$A:$E") 'Change the Sheet name and data range accordingly
wbk1.Close
Set wbk2 = Workbooks.Open(strSecondFile)
wbk2.Sheets("Sheet1").Range("A2:E500000").Copy wbk3.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 'Change the Sheet name and data range accordingly
wbk3.Save
wbk3.Close
'Restoring Speeding up the macro
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAuto
ActiveSheet.DisplayPageBreaks = True
End Sub
Bookmarks