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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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"
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

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


"Pete_UK" 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

On 29 Ago, 03:44, GS wrote:

* 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

On Aug 28, 8:04*pm, Pete_UK wrote:
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

Scossa submitted this idea :
On 29 Ago, 03:44, GS wrote:

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

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?"
<g


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

Bye!
Scossa

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

Pete

On Aug 29, 2:04*am, Pete_UK wrote:
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

On 29 Ago, 21:33, GS wrote:

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

Scossa pretended :
On 29 Ago, 21:33, GS wrote:

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.

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!


Just for clarity, not for flame.

Bye!
Scossa

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 30 Ago, 16:52, GS wrote:

..just for clarity!


Ok.

Bye!
Scossa