FIND IF WKSHT EXSISTS NOT WORKING

JonRowland

Active Member
Joined
May 9, 2003
Messages
415
Office Version
  1. 365
Platform
  1. Windows
I've had the following piece of code in procedure for a long time without any problem. However, all of a sudden the Set WkSheet entry is causing a 'Run Time Error "9': Subscript out of range'. I'm confused why.

What the code is doing is checking to see if a Worksheet call Data exsists and if so then to delete and so the code can procede, starting by adding a new WkSht of same name.

Any ideas why all of a sudden this would stop working? I'm running Excel version Microsoft 365 MSO (Version 2110 Build 16.0.14527.20234) 32-bit.

VBA Code:
Dim WkSheet as WorkSheet

       On Error Resume Next

        Set WkSheet = Sheets("Data")
     
        If WkSheet Is Nothing Then 
            ' If WkSht doesn't exist then do nothing & continue procedure
            Set WkSheet = Nothing
            On Error GoTo 0
        Else
            ' If exists then delete it
            Sheets("Data").Delete
            Set WkSheet = Nothing
            On Error GoTo 0
        End If

Sheets.Add.Name = "Data"
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
all of a sudden the Set WkSheet entry is causing a 'Run Time Error "9': Subscript out of range'. I'm confused why.
Seems unlikely because of the preceding On Error Resume Next.
Nevertheless, you could try this macro.

VBA Code:
Sub JonRowland()
    Dim WkSheet As Worksheet

    On Error Resume Next
    Set WkSheet = ThisWorkbook.Sheets("Data")
    On Error GoTo 0

    If Not WkSheet Is Nothing Then
        ' If exists then delete it without warning
        Application.DisplayAlerts = False
        WkSheet.Delete
        Set WkSheet = Nothing
        Application.DisplayAlerts = True
    End If
    ThisWorkbook.Sheets.Add.Name = "Data"
End Sub
 
Upvote 0
Can you check in VBE under Tools > Options > General, that you don't have Break on All Errors ticked.
The standard setting is Break on Unhandled Errors.

1636778671178.png
 
Upvote 0
Solution
Alex, Yes I had turned on Break on All Errors for some reason. Problem solved. Cheers
 
Upvote 0
This is shorter and does the same:
VBA Code:
Sub WkShDel()
  Application.DisplayAlerts = False
  On Error Resume Next
  ThisWorkbook.Sheets("Data").Delete
  ThisWorkbook.Sheets.Add.Name = "Data"
  On Error GoTo 0
  Application.DisplayAlerts = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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