Runtime Error 9 Subscript Out of Range ... sometmes?

[ColoKevMan]

The below code runs flawlessly on my laptop. When a co-worker, on same network with same access/permissions, runs the macro, it gets the Runtime Error 9 on the Master.Sheets("Buyers IRs").Select row. It's like it can't find the worksheet, but it does easily on my machine. Thoughts?

Sub CopyBuyersIRsc()
' Copy Buyers IR tab from Master
Application.ScreenUpdating = False
Dim Currentwb As Workbook
Dim Master As Workbook
Set Currentwb = ThisWorkbook
Set Master = Workbooks.Open("some sharepoint link/workbook name")
Dim lRowEnd As Long
'
    Master.Sheets("Buyer IRs").Select
    Sheets("Buyer IRs").Copy After:=Currentwb.Sheets("Sherry C")
lRowEnd = Range("A60000").End(xlUp).Row
    Range("$K$2:$K$" & [lRowEnd]).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
    Master.Close savechanges:=True
    Sheets("Sherry C").Activate
    Range("G9").Select
Application.ScreenUpdating = True
End Sub

 

[rlv01]

@NdNoviceHlp I have no doubt your suggestion is a much more efficient way to accomplish that copy. But that is not where the Run-time error is occurring. Per the highlight from the Debug on the Run-time error, it is the "Master.Sheets("Buyer IRs").Select" line that is throwing the error. Again, the code runs fine on my laptop, errors when a coworker initiates the same macro from the same workbook.

You should at least give my code a try to help you undersand where things are going wrong with yours.

 

[Peter_SSs]

@NdNoviceHlp
Since the forum's underlying software was changed 3 or 4 years ago, the available code tags have changed. Perhaps you could have a look at the currently available options - see info & link in my signature block. Compare the code formatting in posts 7 & 8 to see some of the differences.

 

[NdNoviceHlp]

Hmmm... member since 2002, almost 3600 posts... I guess you could say that I'm a bit old school. I guess I could trial to fancy things up but I'm fairly sure it won't improve my coding (unfortunately) Have a nice day @Peter_SSs Dave

 

[ColoKevMan]

You should at least give my code a try to help you undersand where things are going wrong with yours.

Thanks rlv01!! Your code did indeed isolate that the "Buyers IRs" worksheet was the issue. All I did was look at the path to the sharepoint site where the workbook is located. It has some special characters (?, =,) after the filename.extension. I included those characters in the vba and it seems to work. Really appreciate the insight. I will have to research the differences between implied and explicit ranges.