Create Worksheet BUT If It Already Exists...
 

I have the following:

Sub EXAMPLE()
Dim 1WorkSheet As String
1WorkSheet = "Sheet One"
Sheets.Add.Name = 1WorkSheet
Worksheets(1WorkSheet).Visible = xlSheetHidden
End Sub

How do I check, before 1WorkSheet is created, that there is not already a
sheet called "Sheet One". And if there is, use the existing sheet, first of
all erasing data in it and carrying on as normal?

Thanks!
Dave

Create Worksheet BUT If It Already Exists...
 

Here's how I would go about it.


Public Sub EXAMPLE()
Dim wsh As Excel.Worksheet

Dim strName As String
strName = "Sheet One"

On Error Resume Next
Set wsh = ThisWorkbook.Worksheets(strName)
On Error GoTo 0

If wsh Is Nothing Then
' does not exist
Set wsh = ThisWorkbook.Worksheets.Add
wsh.Name = strName
Else
wsh.UsedRange.Clear
End If

wsh.Visible = xlSheetHidden
End Sub


On Oct 30, 5:55 pm, Dave wrote:
I have the following:

Sub EXAMPLE()
Dim 1WorkSheet As String
1WorkSheet = "Sheet One"
Sheets.Add.Name = 1WorkSheet
Worksheets(1WorkSheet).Visible = xlSheetHidden
End Sub

How do I check, before 1WorkSheet is created, that there is not already a
sheet called "Sheet One". And if there is, use the existing sheet, first of
all erasing data in it and carrying on as normal?

Thanks!
Dave

Create Worksheet BUT If It Already Exists...
 

Adapt this to suit
Sub ifsheet()
Application.DisplayAlerts = False
Dim mySheet As String
mySheet = "sheet7"
If mySheet = "" Then Exit Sub
On Error Resume Next
If Sheets(mySheet) Is Nothing Then
MsgBox "no"
'sheets.add
Else
MsgBox "Yes"
'do your thing
'sheets(mysheet).cells.clear'or?

End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave" wrote in message
...
I have the following:

Sub EXAMPLE()
Dim 1WorkSheet As String
1WorkSheet = "Sheet One"
Sheets.Add.Name = 1WorkSheet
Worksheets(1WorkSheet).Visible = xlSheetHidden
End Sub

How do I check, before 1WorkSheet is created, that there is not already a
sheet called "Sheet One". And if there is, use the existing sheet, first
of
all erasing data in it and carrying on as normal?

Thanks!
Dave