Adding a sheet and checking it does not already exist when is hidden

[tonywatsonhelp]

Hi Everyone,

I'm getting a bit confused and need help (so my friends keep telling me )

I have a workbook and want to create a new sheet using vba if the sheets not already there but the sheets are hidden?

so any ideas how i can do this?

lets say the sheet is called "Tony1"

Thanks

Tony

 

[mumps]

Re: Addibg a sheet and checking it does not already exist when is hidden

Try this macro. It will detect whether or not the sheet already exists. It doesn't matter if it's hidden or not.

Sub Tony()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, response As String
    response = InputBox("Please enter the name of the sheet to add.")
    Set ws = Nothing
    On Error Resume Next
    Set ws = Worksheets(response)
    On Error GoTo 0
    If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = response
    Else
        MsgBox ("A sheet already exists with the name " & response & ".")
    End If
End Sub

 

[dmt32]

Re: Addibg a sheet and checking it does not already exist when is hidden

Hi,
similar

Sub AddSheet()
    Dim SheetName As Variant
    Do
        SheetName = InputBox("Please Enter Sheet Name To Add", "Add Sheet")
'cancel pressed
        If StrPtr(SheetName) = 0 Then Exit Sub
        If Len(SheetName) > 0 Then
            If Not Evaluate("ISREF('" & SheetName & "'!A1)") Then
                Exit Do
            Else
                MsgBox SheetName & Chr(10) & "Sheet Name Exists", 48, "Sheet Exists"
            End If
        End If
    Loop


    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = SheetName


End Sub

Dave

 

[tonywatsonhelp]

Re: Addibg a sheet and checking it does not already exist when is hidden

Thank you very much this is very helpful,
I don't need it to tell me if the sheet exists just get on with the macro but look at both your codes I can work out how to do what I wanted so amasive thank you to both of you for your help.
Thanks
Tony

 

[dmt32]

Re: Addibg a sheet and checking it does not already exist when is hidden

welcome - glad we could help

Many thanks for feedback

Dave