Hi I get a runtime error '9' - Subscript out of range in running the following code for the line (wbk1.Sheets("Sheet1").Range("$A:$K").Copy wbk3.Sheets("Copy").Range("$A:$K") 'Change the Sheet name and data range accordingly)
Sub CombineWBks()
Dim strFirstFile, strSecondFile, strThirdFile, dialogTitle As String
Dim wbk1, wbk2, wbk3 As Workbook
Dim fileDialog As fileDialog
'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
dialogTitle = "Navigate to and select required file."
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
'Change file path to match your files locations
With fileDialog
.InitialFileName = "C:\Users\IsuruGaj\Downloads"
'.InitialFileName = ThisWorkbook.Path & "\" 'Alternative to previous line
.AllowMultiSelect = True
.Filters.Clear
.Title = dialogTitle
If .Show = False Then
MsgBox "File not selected to import. Process Terminated"
Exit Sub
End If
strFirstFile = .SelectedItems(1)
strSecondFile = .SelectedItems(2)
End With
strThirdFile = "X:\EAPM\Command Centre\Status report\TfNSW CoE - Weekly Remedy Status Report_version4.xlsm"
Set wbk1 = Workbooks.Open(Filename:=strFirstFile)
'Set wbk_2 = Workbooks.Open(Filename:=strSecondFile)
Set wbk3 = Workbooks.Open(strThirdFile)
' 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
'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:$K").Copy wbk3.Sheets("Copy").Range("$A:$K") 'Change the Sheet name and data range accordingly
wbk1.Close
Set wbk2 = Workbooks.Open(Filename:=strSecondFile)
wbk2.Sheets("Sheet1").Range("$A2:$K").Copy wbk3.Sheets("Copy").Range("A2" & 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