If named sheet exists then delete it

[Pete_UK]

I am creating a new sheet in a macro and then renaming it to Test with
the following lines:

Sheets.Add
ActiveSheet.Name = "Test"

However, there might already be a sheet called Test in the workbook
from an earlier (failed) run of the macro. How can I detect if that
sheet exists and delete it if it does exist before the above lines of
code?

Many thanks,

Pete

 

[GS]

Pete_UK presented the following explanation :

I am creating a new sheet in a macro and then renaming it to Test with
the following lines:

Sheets.Add
ActiveSheet.Name = "Test"

However, there might already be a sheet called Test in the workbook
from an earlier (failed) run of the macro. How can I detect if that
sheet exists and delete it if it does exist before the above lines of
code?

Many thanks,

Pete

Air code...
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = "Test" Then
Application.DisplayAlerts = False
wks.Delete
Application.DisplayAlerts = True
Exit For
End If
Next wks

 

[Jim Cone]

Function SheetExists(ByRef shtName As String, _
Optional ByRef wbName As String) As Boolean
If LenB(wbName) < 1 Then wbName = ActiveWorkbook.Name
On Error Resume Next
SheetExists = CBool(LenB(Workbooks(wbName).Sheets(shtName).Name))
End Function
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..

"Pete_UK" <[email protected]>
wrote in message
I am creating a new sheet in a macro and then renaming it to Test with
the following lines:

Sheets.Add
ActiveSheet.Name = "Test"

However, there might already be a sheet called Test in the workbook
from an earlier (failed) run of the macro. How can I detect if that
sheet exists and delete it if it does exist before the above lines of
code?
Many thanks,
Pete

 

[Charlotte E]

Instead of all these functions, loops, and other 'advanced' solutions you
often see for solving this problem, I would take advantage of the fact, that
Excel always activate and jump to any newly added sheet, and then check if
that sheetname is 'Test'.

The macro would look something like this:
____________________________________________________________
On Error Resume Next ' If sheet exsists
Application.DisplayAlerts = False ' If sheet must be deleted

Sheets.Add.Name = "Test"
If ActiveSheet.Name <> "Test" Then ActiveSheet.Delete
Sheets("Test").Select
____________________________________________________________

Only 3 quick lines, but it gets the job done

CE

 

[Scossa]

  For Each wks In ActiveWorkbook.Worksheets
    If wks.Name = "Test" Then

Test not needed (IMHO), simply:

On Error Resume Next 'to prev. if not exist
Application.DisplayAlerts = False
wks.Delete
Application.DisplayAlerts = True
On Erro Goto 0

Bye!
Scossa

 

[Don Guillett Excel MVP]

I am creating a new sheet in a macro and then renaming it to Test with
the following lines:

    Sheets.Add
    ActiveSheet.Name = "Test"

However, there might already be a sheet called Test in the workbook
from an earlier (failed) run of the macro. How can I detect if that
sheet exists and delete it if it does exist before the above lines of
code?

Many thanks,

Pete

Modify this to suit
Sub checksheet()
On Error Resume Next
If IsError(ActiveWorkbook.Worksheets("Notes")) Then
MsgBox "Does not Exist"
Else
MsgBox "exists"
End If
End Sub

 

[GS]

Scossa submitted this idea :

Test not needed (IMHO), simply:

The OP's request was:
"How can I detect if that sheet exists and delete it if it does exist
before the above lines of code?"

 

[Pete_UK]

Many thanks everyone for your suggestions. I think I need to become
more familiar with On Error !!

Pete

 

[Scossa]

I apologize in advance for my bad english.

The OP's request was:
"How can I detect if that sheet exists and delete it if it does exist
before the above lines of code?"

Ok, but if O.P. run your code or run my code, he can not understand
based on the results obtained if runned your code or my code.
So ... why loops across all sheets if code not advise user that
founded sheet?

Just for clarity, not for flame.

Bye!
Scossa

 

[GS]

Scossa pretended :

I apologize in advance for my bad english.


Ok, but if O.P. run your code or run my code, he can not understand
based on the results obtained if runned your code or my code.

If the sheet already existed then it would be deleted. The OP wanted to
know how to detect if the sheet exists. I merely presented one way.
Others have shown alternative methods. I'm afraid I don't get what's
not to understand!

So ... why loops across all sheets if code not advise user that
founded sheet?

As mentioned, this is just one way to test if a sheet exists. There are
better (more efficient) ways as others have shown. The OP did not ask
for a means to notify the user if the sheet did already exist.

<IMO>
If the sheet already existed I would clear its contents and use it. If
it didn't exist then I would add it. The code require to do it this way
would/could be more efficient, and better self-documenting. No need to
use error handling for that purpose since it can be done within a
reusable function like this:

Function bSheetExists(WksName As String) As Boolean
' Checks if a specified worksheet exists.
' Arguments: WksName The name of the worksheet
' Returns: TRUE if the sheet exists

Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(WksName)
bSheetExists = (Err = 0)

End Function

OR this one-liner:
Function WorksheetExists(WSName As String, Optional wb As
Excel.Workbook = Nothing) As Boolean '//by Chip Pearson
On Error Resume Next
WorksheetExists = CBool(Len(IIf(wb Is Nothing, ThisWorkbook,
wb).Worksheets(WSName).name))
End Function

And as for an example of using either of these:
If bSheetExists("Test") Then
'If WorksheetExists("Test", ActiveWorkbook) Then 'optional method
Set wksTarget = Sheets("Test")
wksTarget.UsedRange.ClearContents
Else
Set wksTarget = ActiveWorkbook.Sheets.Add
wksTarget.Name = "Test"
End If

...just for clarity!

 

[Scossa]

..just for clarity!

Ok.

Bye!
Scossa