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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

VAndre

New Member
Joined
Dec 7, 2005
Messages
42
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
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,753
Members
419,525
Latest member
helensesc

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
Top