Adding a Sheet if It's Not There to Delete

[Guzzlr]

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And (sht.Name = "NonFormat") Then
        sht.Delete
    End If
Next sht
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "NonFormat"

Hello All,
The following will delete the NonFormat sheet, and then add a new sheet...whichis what I want. What if there is no NonFormat sheet to delete, it will fault on me.
How do I get excel to automatically add the NonFormat sheet, if one does not exist to delete?
Thanks for the help

 

[Joe4]

One way is to create your own function to check if a sheet exists, and use that in your code.
See here: https://www.exceltip.com/files-work...-a-workbook-using-vba-in-microsoft-excel.html

 

[Guzzlr]

One way is to create your own function to check if a sheet exists, and use that in your code.
See here: https://www.exceltip.com/files-work...-a-workbook-using-vba-in-microsoft-excel.html

I'm not following this, if the worksheet does not exist, why cant I have code to add the worksheet? Maybe some sort of ElseIf statement?
Thanks

 

[Joe4]

You asked this:

How do I get excel to automatically add the NonFormat sheet, if one does not exist to delete?

You can use the code contained it that links that shows you how to check if a sheet exists or not.
So you can use an IF statement to see if a sheet by that names exists, and if not, you can add the sheet.

 

[Guzzlr]

Dim wSht As Integer
wSht = Application.Sheets.Count
worksheetexists = False
    For x = 1 To wSht
    If Worksheets(x).Name = "NonFormat" Then
        worksheetexists = True
    Exit For
    End If

    Next x
    If worksheetexists = False Then
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "NonFormat"
'    Exit Sub
    End If

I'm using the code above, which is working, however, I'm getting a variable not defined in "worksheetexists = False". How do I define this variable?
Thanks
Also, the code above does work without Option Explicit.
Thanks for the help

 

[Fluff]

How about

Sub AddSheet()
   If Not Evaluate("isref(NonFormat!A1)") Then Sheets.Add(, Sheets(Sheets.count)).Name = "NonFormat"
End Sub

 

[Joe4]

I'm using the code above, which is working, however, I'm getting a variable not defined in "worksheetexists = False". How do I define this variable?
Thanks
Also, the code above does work without Option Explicit.

With "Option Explicit" turned on (which is recommended), you are required to declare variable before you use them (with a "Dim" statement).
You already did that with your wSht variable. You need to do the same with the worksheetexists variable, like this:

Dim worksheetexists as Boolean

 

[My Aswer Is This]

Here is what I use to see if a sheet exist:

Sub Does_Sheet_Exist()
'Modified  8/30/2018  9:14:28 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
x = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Name = "Me" Then x = 1
    Next
If x = 0 Then Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Me"
Application.ScreenUpdating = True
End Sub

 

[Guzzlr]

Dim worksheetexists as Boolean

That's what I was looking for
Thanks to all