Subscript out of range error when replacing ThisWorkbook.Activate with wb.Activate

I'm very new to VBA, so this is likely a simple question to answer, but I couldn't find it while googling. I have a Sub that is working fine when I use ThisWorkbook.Activate but refuses to run if I replace it with a direct reference to the workbook, and I can't figure out why.

Version info: Microsoft® Excel® for Microsoft 365 MSO (Version 2501 Build 16.0.18429.20132) 64-bit

Non-working code

Sub Paste_Columns()

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual
    
    Dim tgtWB As Workbook
    Dim tgtFilePath As String
    Dim cell As Range
    Dim lastRow As Long
    Dim srcWB As Workbook
    Dim srcFilePath As String
    
    tgtFilePath = "\\location.com\tgtFile.xlsx"
    srcFilePath = "https://org-my.sharepoint.com/personal/Documents/Desktop/srcFile.xlsm"
    
    Set tgtWB = Workbooks.Open(tgtFilePath)
    Set srcWB = Workbooks(srcFilePath)
    
    srcWB.Activate
    
    Union(Range("Tbl1[[#Headers],[#Data],[Column3]]"), _
            Range("Tbl1[[#Headers],[#Data],[Column6]]"), _
            Range("Tbl1[[#Headers],[#Data],[Column8]]"), _
            Range("Tbl1[[#Headers],[#Data],[Column12]]")).Select
    Selection.Copy

    tgtWB.Worksheets(4).Activate
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Dim tbl As ListObject
    Dim rng As Range

    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.TableStyle = "TableStyleMedium2"
        

End Sub

Working code

Sub Paste_Columns()

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual
    
    Dim tgtWB As Workbook
    Dim tgtFilePath As String
    Dim cell As Range
    Dim lastRow As Long
    Dim srcWB As Workbook
    Dim srcFilePath As String
    
    tgtFilePath = "\\location.com\tgtFile.xlsx"
    srcFilePath = "https://org-my.sharepoint.com/personal/Documents/Desktop/srcFile.xlsm"
    
    Set tgtWB = Workbooks.Open(tgtFilePath)
    Set srcWB = Workbooks.Open(srcFilePath)
    
    ThisWorkbook.Activate
    
    Union(Range("Tbl1[[#Headers],[#Data],[Column3]]"), _
            Range("Tbl1[[#Headers],[#Data],[Column6]]"), _
            Range("Tbl1[[#Headers],[#Data],[Column8]]"), _
            Range("Tbl1[[#Headers],[#Data],[Column12]]")).Select
    Selection.Copy

    tgtWB.Worksheets(4).Activate
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
    Dim tbl As ListObject
    Dim rng As Range

    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.TableStyle = "TableStyleMedium2"
        

End Sub

Solution

Assuming you are receiving "Run-time error 9 (Subscript out of range)", the issue is your reference to the source workbook.

Set srcWB = Workbooks(srcFilePath)

The above line does not work because Workbooks() is a "collection that represents all the open workbooks". As a callable function, it is expecting a file name (including the extension if the file was previously saved) or an index number for the correct open workbook—not a file path. See documentation.

Solution

For the workbook calling the macro use one of the following:

Set WB_Macro = ThisWorkbook
Set WB_Macro = Workbooks("name_of_macro_workbook")
Set WB_Macro = Workbooks.Open(path_to_macro_WB)
Set WB_Macro = ActiveWorkbook ' Not ideal as the active WB changes

For other workbooks, opening and setting their reference as you did works fine

Set WB_Data = Workbooks.Open(path_to_data_WB)

If all workbooks are open and their references set properly, srcWB.Activate should work fine.