Check if Sheet exists

[Anerol]

Hi,

I'd like to write a macro that checks to see if a certain sheet already exists. If it doesn't I want it to create the sheet, and if it does, then move on.

HELP ME PLEASE!!!

 

[onlyadrafter]

Hello,

Something like this perhaps?

Sub ADD_SHEET()
CURRENT_SHEET = ActiveSheet.Name
On Error Resume Next
Sheets("Sheet4").Select
Sheets.Add
Sheets(CURRENT_SHEET).Select
End Sub



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[Anerol]

But where do I specify the name of the sheet to look for?

 

[onlyadrafter]

Hello,

The line

Sheets("Sheet4").select is the one that needs changing the "Sheet4" part needs to be a sheet name or a variable in the macro.



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[jhartis]

There may be a better way, but

exists = False
For Each wkSheet in Thisworkbook.Worksheets
    if wkSheet.Name = "Sheet4" Then
       exists = True
       End If
    Next

If exists = False then
    Thisworkbook.Sheets.Add Before:= _
        Worksheets(Worksheets.Count)
    ThisWorkbook.ActiveSheet.Name = "Sheet4"
    End IF

 

[smanguns]

You can use also the following function

Public Function IsSheetExists(sname) As Boolean
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then IsSheetExists = True _
Else IsSheetExists = False
End Function

have fun
Sandra