Create Worksheet BUT If It Already Exists...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top