VBA - Subscript Out of Range Error When Checking if a Worksheet Exists

[beartooth91]

I'm getting the 'subscript out of range' error on the line below and I'm not real sure why. Tried a few different things and still no success. Any ideas?

sheetExist = (wbMaster.Sheets(wsname).Index > 0) '<------- Getting 'subscript out of range' error

The entire sub is below.

Sub Import_IO_Lists()
' Opens each workbook in the Standard-Format IO Lists subfolder and copies each worksheet into the NIC Master IO List workbook
'
Application.ScreenUpdating = False

'Declare Variables------------------------------------------------------------------------------
Dim sheetExist As Boolean
Dim StartRow As Long, LastRow As Long, sRow As Long
Dim SfFolder As String, SfList As String, wbname As String, wsname As String
Dim nme() As String
Dim wbMaster As Workbook

SfFolder = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists")
SfList = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists" & "\*.xlsx")

Set wbMaster = Workbooks("NIC Master IO List.xlsm")
'-----------------------------------------------------------------------------------------------
'Loop through the Standard-Format IO Lists subfolder, copy each workbook to a IO sheet in the Master IO List workbook

Do While SfList <> ""
  With Workbooks("NIC Master IO List.xlsm").Sheets(1)
    Sheets(1).Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = "NewSheet"
    StartRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
  End With
  Workbooks.Open FileName:=ThisWorkbook.Path & "/Standard-Format IO Lists" & "\" & SfList  'Open each workbook, one at a time
  wbname = ActiveWorkbook.Name  ' Capture newly-opened workbook name
  With Workbooks(wbname).Sheets(1)
    'LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
    nme = Split(.Cells(11, 2).Value, "-", -1)
    wsname = nme(1)
  End With
    
    sheetExist = (wbMaster.Sheets(wsname).Index > 0) '<------- Getting 'subscript out of range' error
  
  With Workbooks(wbname).Sheets(1)
    LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
    If Not sheetExist Then
      Sheets(1).Range("B11:BO" & LastRow).Copy Destination:=wbMaster.Worksheets("NewSheet").Range("B" & StartRow)
    Else
      Sheets(1).Range("B11:BO" & LastRow).Copy Destination:=wbMaster.Worksheets(wsname).Range("B" & sRow)
    End If
    Workbooks(wbname).Close
  End With
  
  With Workbooks("NIC Master IO List.xlsm").Worksheets("NewSheet")
    If Not sheetExist Then
      Worksheets("NewSheet").Name = wsname
    Else
      Worksheets("NewSheet").Delete
    End If
  End With
  'wsname = nme(1)
  SfList = Dir
  
Loop
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Change this:

sheetExist = (wbMaster.Sheets(wsname).Index > 0)

For this:

sheetExist = Evaluate("ISREF('" & wsname & "'!A1)")

 

[beartooth91]

Change this:

sheetExist = (wbMaster.Sheets(wsname).Index > 0)

For this:

sheetExist = Evaluate("ISREF('" & wsname & "'!A1)")

Why - and what is - !A1?

 

[beartooth91]

Well, I got it all to work..... I absolutely hate marking my own solution (seriously). Plus, the below code is ummm......likely not near as concise as it could be. So, if anyone posts a better potential solution, I'' try it and update the solution (post).

BTW, this is a bulk copy/paste update, from worksheets in a subfolder, to a master sheet.

Sub Import_IO_Lists()
' Opens each workbook in the Standard-Format IO Lists subfolder and copies each worksheet into the NIC Master IO List workbook
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Declare Variables-------------------------------------------------------------------------------
Dim sheetExist As Boolean
Dim StartRow As Long, LastRow As Long, sRow As Long
Dim SfFolder As String, SfList As String, wbname As String, wsname As String
Dim nme() As String
Dim wbMaster As Workbook
Dim wS As Worksheet

SfFolder = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists")
SfList = Dir(ThisWorkbook.Path & "/Standard-Format IO Lists" & "\*.xlsx")

Set wbMaster = Workbooks("NIC Master IO List.xlsm")
'-----------------------------------------------------------------------------------------------
'Loop through the Standard-Format IO Lists subfolder, copy each workbook to a IO sheet in the Master IO List workbook

Do While SfList <> ""
  With Workbooks("NIC Master IO List.xlsm").Sheets(1)
    Sheets(1).Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = "NewSheet"
    StartRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
  End With
  Workbooks.Open FileName:=ThisWorkbook.Path & "/Standard-Format IO Lists" & "\" & SfList  'Open each workbook, one at a time
  wbname = ActiveWorkbook.Name  ' Capture newly-opened workbook name
  With Workbooks(wbname).Sheets(1)
    'LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
    nme = Split(.Cells(11, 2).Value, "-", -1)
    wsname = nme(1)
  End With
  Workbooks("NIC Master IO List.xlsm").Activate
  With Workbooks("NIC Master IO List.xlsm")
    For Each wS In .Worksheets
      If wS.Name = wsname Then
         sheetExist = True
         GoTo ThisLine
      Else
         sheetExist = False
      End If
    Next
  End With
    'sheetExist = (wbMaster.Sheets(wsname).Index > 0) '<------- Getting 'subscript out of range' error
    'sheetExist = wbMaster.Worksheets(wsname).Index
ThisLine:
  Workbooks(wbname).Activate
  With Workbooks(wbname).Sheets(1)
    LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
    If sheetExist = False Then
      Sheets(1).Range("B11:BO" & LastRow).Copy Destination:=Workbooks("NIC Master IO List.xlsm").Worksheets("NewSheet").Range("B" & StartRow)
    Else
      sRow = Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Cells(Rows.Count, "B").End(xlUp).Row + 1
      Sheets(1).Range("B11:BO" & LastRow).Copy Destination:=Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("B" & sRow)
    End If
    Workbooks(wbname).Close
  End With
  
  With Workbooks("NIC Master IO List.xlsm").Worksheets("NewSheet")
    If sheetExist = False Then
      Worksheets("NewSheet").Name = wsname
    Else
      Worksheets("NewSheet").Delete
    End If
  End With
  
  SfList = Dir
  
Loop
Application.DisplayAlerts = False
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

if anyone posts a better potential solution

Try:

  On Error Resume Next
  sheetExist = Evaluate("ISREF('[" & wbMaster.Name & "]" & wsname & "'!A1)")
  On Error GoTo 0

A1, is cell A1, check if there is a reference in sheet-cell A1

 

[beartooth91]

Try:

  On Error Resume Next
  sheetExist = Evaluate("ISREF('[" & wbMaster.Name & "]" & wsname & "'!A1)")
  On Error GoTo 0

A1, is cell A1, check if there is a reference in sheet-cell A1

That's what I thought.....but, no, none of them have the sheet title in A1.....some of them have it in B1, but many do not....no consistency.

 

[Alex Blakenburg]

That's what I thought.....but, no, none of them have the sheet title in A1.....some of them have it in B1, but many do not....no consistency.

I'm afraid that is not what Dante meant.
The syntax of ISREF is ISREF(Cell_Address).
If the Cell_Address used is a valid cell address it returns True, if the address is not valid it returns False.
A1 could be swapped out for any valid cell address.
Since we know that A1 is valid if ISREF(Worksheet_Name!A1) returns False we know it's the worksheet_name that is invalid and as such it doesn't exist.
To see the format required for the Cell address just type = in the cell and then refer to any cell in another sheet.
If the sheet name has a space in it, it requires single quotes around the name eg =ISREF('Sheet 2'!A1)
Since with quotes it works for with and without spaces it is safer to always use the quotes.
You are referring to a different workbook so Dante has also included the reference to the workbook which needs to be included in square brackets and inside the single quotes.

Sub test_isRef()

' Sheet3 does NOT exist
Debug.Print Evaluate("ISREF(Sheet3!A1)")
' Returns False - in your case sheetExist is False

' Sheet 2 does exist
Debug.Print Evaluate("ISREF('Sheet 2'!A1)")
' Returns True - in your case sheetExist is True

End Sub

Note: It behaves slightly differently when you reference another workbook in that if the reference is not valid instead of False it returns Error 2015 which is why Dante has included the On Error code.