Correct Syntax for Named Worksheet

I have named a worksheet as "TestSheet".

I want to write a formula to delete:

Dim ws as Worksheet
If ws.Name = "TestSheet" Then ws.Delete

What is correct syntax to do this?

Thank you.

Just to further clarify, by "named worksheet" I mean naming it in VB Editor under Excel Objects Properties. I do not mean the name given on the tab.

Originally Posted by MarvinP

Hi and welcome to the forum,

See if this does what you want:

Please Login or Register  to view this content.

Not exactly what I was looking for. I omitted important details in my OP which I subsequently posted. By "named" sheet I am referring to the CodeName which is defined in VBE Properties as opposed to the name on the sheet tab.

How would I modify the code to the refer to the CodeName?

Thanks.

From what I understand, a worksheet can be identified by either the Name on the tab (e.g. Settings) given it by the User, or it can be identified by the (Name) assigned to it by Excel (e.g. Sheet1, Sheet2, etc.).

You can rename the (Name) from Sheet1 to say TestSheet in VB Editor so that you don't have to remember that it was Sheet1.

For the tab Name (e.g. Settings), your code works.

However, if I want to use its (Name) (e.g. Sheet1) how would I write it.

For instance, I tried
If Worksheets(ShCtr).Name = "Sheet1" Then
Worksheets(ShCtr).Delete

but this didn't work.

thanks.

I finally figured it out. It is the .CodeName property. What I was looking for is:

Dim ws As Worksheet

For Each ws In Worksheets
If ws.CodeName = "TestSheet" Then ws.Delete
Next ws

Thanks for you help!