"Subscript out of Range" error when testing for Worksheets

[krissz]

This relates to a project which uses multiple sheets, named SL 1, SL 2, ..., SL 99, SL 100, etc. This is a contiguous sequence.
A section of code is supposed to scan through all the sheets to identify the final sheet, whereupon other actions will occur. This fails with "Run Time Error '9'" on the line "Set Ws_Sheet = Sheets("SL " & Ws_No)".
Please, what have I done wrong ?
The full code is:

Sub Check_PL_Ws()
      ' Check whether a PL xx Sheet exists & whether it is populated
      
      Dim Ws_No As Integer
      Dim Ws_Nam As String
      Dim Ws_Exist As Boolean
      Dim Ws_Sheet As Worksheets
      
      Ws_Exist = True
      Ws_No = 1
      
      Do While Ws_Exist
            
            [I][COLOR=Red]Set Ws_Sheet = Sheets("SL " & Ws_No)[/COLOR][/I]
            
            ' Does not exist
            If Ws_Sheet Is Nothing Then
            
                  Ws_Nam = "SL " & Ws_No
                  Exit Sub
            
           ' Exists
            Else
            
                  Ws_Sheet.Activate             ' For test purposes only
                  Ws_No = Ws_No + 1
            End If
            
      Loop
      
      MsgBox "Sheet SL " & Ws_No & " does not exist"
      
End Sub

This is not new code - I have found similar examples on various sites.

 

[John_w]

Dim Ws_Sheet As Worksheets

should be

Dim Ws_Sheet As Worksheet

Use this function to check whether a worksheet exists:

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
    On Error Resume Next
    WorksheetExists = (Sheets(WorksheetName).Name <> "")
    On Error GoTo 0
End Function

Or, to determine the name of the last worksheet, try:

Sheets(Sheets.Count).Name

 

[krissz]

John
Thank you for your help. I have now resolved it - it was finger & brain trouble. My sheets are named "PL xx" while the code was looking for sheets named "SL xx" !?
Your abbreviated code works perfectly & does exactly what I need.