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

beartooth91

Board Regular
Joined
Dec 15, 2024
Messages
64
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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?

VBA Code:
sheetExist = (wbMaster.Sheets(wsname).Index > 0) '<------- Getting 'subscript out of range' error
The entire sub is below.

VBA Code:
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
 
I checked your code further and you have a Do While statement:



Then you have to turn off the exists variable before checking the sheet:

Like this:
Rich (BB code):
  sheetExist = False
  On Error Resume Next
  sheetExist = Evaluate("ISREF('[" & wbMaster.Name & "]" & wsname & "'!A1)")
  On Error GoTo 0

Or this:
Rich (BB code):
  sheetExist = False
  For Each wS In Workbooks("NIC Master IO List.xlsm").Sheets
    If wS.Name = wsname Then
      sheetExist = True
      Exit For
    End If
  Next

😇
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I checked your code further and you have a Do While statement:

1736641144928.png
Then you have to turn off the exists variable before checking the sheet:

Like this:
Rich (BB code):
  sheetExist = False
  On Error Resume Next
  sheetExist = Evaluate("ISREF('[" & wbMaster.Name & "]" & wsname & "'!A1)")
  On Error GoTo 0

Or this:
Rich (BB code):
  sheetExist = False
  For Each wS In Workbooks("NIC Master IO List.xlsm").Sheets
    If wS.Name = wsname Then
      sheetExist = True
      Exit For
    End If
  Next

😇
 
Upvote 0

Forum statistics

Threads
1,225,786
Messages
6,187,048
Members
453,402
Latest member
greg_em

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top