"Subscript out of range" error when using variables as worksheet reference

[msthiagu]

Hi. I have been using excel macro as below and experiencing "Runtime error 9: Subscript out of range" error. It happens only when I use a variable to reference a worksheet within a workbook.

 

[msthiagu]

Hi,
try changing this line

Set wsdest1 = wb1.Sheets(shname1)

for this

Set wsdest1 = wb1.Worksheets(CStr(shname1))

and see if resolves your issue

Dave

Tried the same. The error still persists. Attached screenshot of code.

 

[dmt32]

If still getting the error then can only agree with @RoryA that sheet name does not exist. Worth checking the range names for spaces & typo's.
Perhaps if still an issue, helpful if place a copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it.

BTW - when posting code, use code tags from Menu Bar > VBA then place your code between the tags.

Dave

 

[Alex Blakenburg]

If you change the set wsdest1 line to the below, does it still error out ?

        Set wsdest1 = wb1.Sheets(Trim(shname1))

 

[Alex Blakenburg]

If you want to try this:

Sub Service_Transfer_Action_Items()

    Dim wb1 As Workbook
    Dim wscopy1 As Worksheet, wsdest1 As Worksheet, wsininterim1 As Worksheet
    Dim shname1 As String
    Dim a As Long, b As Long, lrow2 As Long, lrow3 As Long
    Dim sFailed As String
   
    Set wb1 = ActiveWorkbook
    Set wscopy1 = wb1.Worksheets("SCM")
    lrow2 = wscopy1.Cells(Rows.Count, "A").End(xlUp).Row
   
    sFailed = "Sheet(s) not found: "
    For a = 3 To lrow2
        shname1 = wscopy1.Cells(a, 1).Value
        On Error Resume Next
            Set wsdest1 = wb1.Sheets(Trim(shname1))
            If Err Then
                sFailed = sFailed & " " & shname1
            End If
        On Error GoTo 0
        With Application
            b = .IfError(.Match(wscopy1.Range("F" & a), wsdest1.Range("D:D"), 0), 0)
        End With
            If b <> 0 Then
                wscopy1.Range("G" & a & ":L" & a).Copy _
                    wsdest1.Range("E" & b & ":J" & b)
            End If
    Next a
   
    If sFailed <> "Sheet(s) not found: " Then
        MsgBox sFailed
    Else
        MsgBox "Completed"
    End If

End Sub

 

[msthiagu]

If you want to try this:

Sub Service_Transfer_Action_Items()

    Dim wb1 As Workbook
    Dim wscopy1 As Worksheet, wsdest1 As Worksheet, wsininterim1 As Worksheet
    Dim shname1 As String
    Dim a As Long, b As Long, lrow2 As Long, lrow3 As Long
    Dim sFailed As String
  
    Set wb1 = ActiveWorkbook
    Set wscopy1 = wb1.Worksheets("SCM")
    lrow2 = wscopy1.Cells(Rows.Count, "A").End(xlUp).Row
  
    sFailed = "Sheet(s) not found: "
    For a = 3 To lrow2
        shname1 = wscopy1.Cells(a, 1).Value
        On Error Resume Next
            Set wsdest1 = wb1.Sheets(Trim(shname1))
            If Err Then
                sFailed = sFailed & " " & shname1
            End If
        On Error GoTo 0
        With Application
            b = .IfError(.Match(wscopy1.Range("F" & a), wsdest1.Range("D:D"), 0), 0)
        End With
            If b <> 0 Then
                wscopy1.Range("G" & a & ":L" & a).Copy _
                    wsdest1.Range("E" & b & ":J" & b)
            End If
    Next a
  
    If sFailed <> "Sheet(s) not found: " Then
        MsgBox sFailed
    Else
        MsgBox "Completed"
    End If

End Sub

Thanks Alex. This has helped me drill down the root cause. Error found to be with a wrong sheet number referenced.

The code works fine now. I have taken cues from your code and made few changes in my code to make it work. Thanks again.

Regards
Thiagarajan M.S.

 

[Alex Blakenburg]

You're welcome, glad we could help.

 

[smozgur]

I have taken cues from your code and made few changes in my code to make it work.

Good to hear you fixed your code and it works without problems now. However, that would be great if you could post the working code to help future readers, then it is perfectly fine to mark it as the solution.
You can also mark another post that helped you most solving your question if you can't share your code. Otherwise, please do not mark a post that doesn't contain a solution.