check to see if a worksheet exists before VBA code deletes i

VAndre

New Member
Joined
Dec 7, 2005
Messages
42
I've got a little code that needs to create a new worksheet in a workbook and give it a certain name. I want to check to see if a worksheet with the new name exists and delete it before the process starts, since things break if i try and rename the new sheet with the same name as a sheet that already exists.

is there a property of worksheets or some other way to quickly see if it exists?

Thanks!

Andre
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Andre, you could use this:

Code:
Application.DisplayAlerts = False
For Each sht In Activeworkbook.Sheets
  If sht.Name = "YourTestName" Then
    sht.Delete
  End If
Next sht
Application.DisplayAlerts = True
Denis
 
Upvote 0
i say again, thank god you Aussies are up!

What i just did was this:

On error resume next
worksheets(stTempSheetName).Delete

which does the trick (since when the error pops up, it just goes to the next step)

is there a problem with this approach? I realize it's not eloquent, but am i stumbling into something serious?

Andre
 
Upvote 0
Hi Andre, that should work fine too -- but watch out! You need to turn error-reporting back on, or it will stay off. Better...

Code:
On Error Resume Next
worksheets(stTempSheetName).Delete 
On Error GoTo 0

Denis
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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