Subscript Out Of Range Error 9

[excelbytes]

I wrote the following code which works perfectly:

Sub CopyData()
'
' CopyData Macro

    Dim WB As Workbook
    Dim WBDest As Workbook
    Dim WS As Worksheet
    
    Set WB = Application.Workbooks("2023 HR Monthly Report - Gramalote.xlsm")
    Set WBDest = Application.Workbooks("Consolidated HR Report.xlsm")
    
    WB.Activate
    Sheets("Gr_L_Headcount").Range("A8:AO53").Copy
    WBDest.Worksheets("Headcount").Range("A170").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    WB.Activate
    Sheets("Gr_L_Training").Range("C7:N29").Copy
    WBDest.Worksheets("Training").Range("C91").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    WB.Activate
    Sheets("Gr_L_Employee Turnover").Range("AL4:AL13").Copy
    WBDest.Worksheets("Employee Turnover").Range("AU19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    WB.Activate
    Sheets("Gr_L_Employee Turnover").Range("AN4:AN13").Copy
    WBDest.Worksheets("Employee Turnover").Range("AU34").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    WB.Activate
    Sheets("Gr_L_Employee Categories").Range("D3:G20").Copy
    WBDest.Worksheets("Categories").Range("P6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    WB.Activate
    Sheets("Headcount Definitions").Range("A1").Select

End Sub

I copied it to another workbook and made only the changes to the WB name, sheet names and range references:

Sub CopyData()
'
' CopyData Macro

    Dim WB As Workbook
    Dim WBDest As Workbook
    Dim WS As Worksheet
    
    Set WB = Application.Workbooks("2023 HR Monthly Report - Columbia EXPATS.xlsm")
    Set WBDest = Application.Workbooks("Consolidated HR Report.xlsm")
    
    WB.Activate
    Sheets("Co_X_Headcount").Range("A8:AO53").Copy
    WBDest.Worksheets("Headcount").Range("AQ64").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    WB.Activate
    Sheets("Co_X_Training").Range("C7:N29").Copy
    WBDest.Worksheets("Training").Range("R35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    WB.Activate
    Sheets("Co_X_Employee Turnover").Range("AL4:AL13").Copy
    WBDest.Worksheets("Employee Turnover").Range("CH19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    WB.Activate
    Sheets("Co_X_Employee Turnover").Range("AN4:AN13").Copy
    WBDest.Worksheets("Employee Turnover").Range("CH34").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    WB.Activate
    Sheets("Co_X_Employee Categories").Range("D3:G20").Copy
    WBDest.Worksheets("Categories").Range("H33").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    WB.Activate
    Sheets("Headcount Definitions").Range("A1").Select

End Sub

But I'm getting a "Subscript Out Of Range Error 9" on this line:

Set WB = Application.Workbooks("2023 HR Monthly Report - Columbia EXPATS.xlsm")

What am I doing wrong?

 

[Joe4]

Is that that EXACT workbook name (check for typos, extra characters, etc) and is it open in that session of Excel?

 

[excelbytes]

Is that that EXACT workbook name (check for typos, extra characters, etc) and is it open in that session of Excel?

I double checked it and it looked right. So now I copied it and pasted it from another source, don't see any changes, but now it works. I don't know, but there must have been some difference. Thanks.