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]

If you get that error on that line then it does probably mean it cannot find worksheet "Buyer IRs". You might try adding an activate statement.

Master.Activate
Master.Sheets("Buyer IRs").Select

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[Peter_SSs]

@ColoKevMan
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ColoKevMan]

@ColoKevMan
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Apologies for omitting code tags.

 

[NdNoviceHlp]

Hi ColoKevMan. It's not clear what you're actually trying to accomplish? You're copying a sheet then transferring data from where to where? Dave

 

[ColoKevMan]

Hi ColoKevMan. It's not clear what you're actually trying to accomplish? You're copying a sheet then transferring data from where to where? Dave

First off ... the Activate statement did not work - same error message on coworkers laptop. Works fine on my laptop.

Attempting to copy a worksheet, then in the newly copied worksheet, need to copy/paste values of a range ($K2$: lRowEnd) within that worksheet over the existing formulas (came over from Master) as they reference/point to the now closed workbook. If I don't, then get #NA returned in that range.

 

[NdNoviceHlp]

I may have misunderstood, but it seems like you could do this...

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, Rng As Range
'
    'Master.Sheets("Buyer IRs").Select
Master.Sheets("Buyer IRs").Copy After:=Currentwb.Sheets("Sherry C")


lRowEnd = Currentwb.Sheets("Buyer IRs").Range("A60000").End(xlUp).Row
Set Rng = Currentwb.Sheets("Buyer IRs").Range("$K$2:$K$" & lRowEnd)
Rng = Rng.Values
    
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
Master.Close savechanges:=True
'Sheets("Sherry C").Activate
Currentwb.Sheets("Sherry C").Range("G9").Select
Application.ScreenUpdating = True
End Sub

Or... you could transfer the sheet in a collection which might work... Or... use Resize and avoid the copy/paste.
HTH. Dave
ps. I think your error was actually on the line below the line you indicated. You didn't specify what wb the sheet to copy was in.

 

[rlv01]

First off ... the Activate statement did not work - same error message on coworkers laptop. Works fine on my laptop.

Attempting to copy a worksheet, then in the newly copied worksheet, need to copy/paste values of a range ($K2$: lRowEnd) within that worksheet over the existing formulas (came over from Master) as they reference/point to the now closed workbook. If I don't, then get #NA returned in that range.

You might try converting all the implied ranges to explicit ranges, getting rid of all the selects, and adding some code to validate the workbook and worksheets needed.
One example:

Sub CopyBuyersIRsc()
' Copy Buyers IR tab from Master
    Dim Currentwb As Workbook
    Dim Master As Workbook
    Dim WS_Buyer As Worksheet, WS_Sherry As Worksheet, WS_New As Worksheet
    Dim CopyRange As Range

    Application.ScreenUpdating = False

    Set Currentwb = ThisWorkbook

    On Error Resume Next                              'make sure worksheet exists
    Set WS_Sherry = Currentwb.Sheets("Sherry C")
    On Error GoTo 0

    If WS_Sherry Is Nothing Then
        MsgBox "Error - Problem finding worksheet 'Sherry C'", vbCritical
        Exit Sub
    End If

    On Error Resume Next                              'make sure workbooks exists and is open
    Set Master = Workbooks.Open("some sharepoint link/workbook name")
    On Error GoTo 0

    If Master Is Nothing Then
        MsgBox "Error - Problem opening Master workbook", vbCritical
        Exit Sub
    End If

    On Error Resume Next                              'make sure worksheet exists
    Set WS_Buyer = Master.Worksheets("Buyer IRs")
    On Error GoTo 0

    If Not WS_Buyer Is Nothing Then
        WS_Buyer.Copy After:=WS_Sherry
        Set WS_New = ActiveSheet

        With WS_New
            Set CopyRange = .Range("K2:K" & .Range("A" & .Rows.Count).End(xlUp).Row)
            CopyRange.Value = CopyRange.Value
        End With
    Else
        MsgBox "Error - Problem finding worksheet 'Buyer IRs'", vbCritical
    End If

    Master.Close savechanges:=False
    WS_Sherry.Activate
    WS_Sherry.Range("G9").Select
    Application.ScreenUpdating = True
End Sub

 

[ColoKevMan]

I may have misunderstood, but it seems like you could do this...

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, Rng As Range
'
    'Master.Sheets("Buyer IRs").Select
Master.Sheets("Buyer IRs").Copy After:=Currentwb.Sheets("Sherry C")


lRowEnd = Currentwb.Sheets("Buyer IRs").Range("A60000").End(xlUp).Row
Set Rng = Currentwb.Sheets("Buyer IRs").Range("$K$2:$K$" & lRowEnd)
Rng = Rng.Values
   
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
Master.Close savechanges:=True
'Sheets("Sherry C").Activate
Currentwb.Sheets("Sherry C").Range("G9").Select
Application.ScreenUpdating = True
End Sub

Or... you could transfer the sheet in a collection which might work... Or... use Resize and avoid the copy/paste.
HTH. Dave
ps. I think your error was actually on the line below the line you indicated. You didn't specify what wb the sheet to copy was in.

@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.

 

[NdNoviceHlp]

Trial riv01's code. You should be able to find out where the error is. As per my previous ps, sometimes XL will indicate an error on the line of code previous to the actual error. My other thoughts are, is the sync on? Maybe some delay is needed for the copy/paste sheet to occur? Dave