VBA sheet duplicate check

[deanw55]

Hi there,
I have a vba script which duplicates a template sheet, then renames it with a name that user types into a cell.
Example:
User inputs the name "Dean" into cell A2 (on sheet "ADD") then runs the macro - a sheet called TEMPLATE is duplicated, then renamed into Dean. I want to check if the name already exists and perform a warning msg box if so. I am using the below code which duplicates the TEMPLATE sheet and instead of giving me a warning it renames it to TEMPLATE (2). Where did I miss the point? Thank you in advance.

 

[RobP]

Hi, normally I use this function routine, which can be tested as a boolean TRUE / FALSE, and called simply by:

If SheetExists(myNewSheetName) Then

Endif

You would set myNewSheetName variable to the value that the user enters into the cell before checking if it exists.
Rgds
Rob

Private Function SheetExists(myNewSheetName) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(Tabname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function

 

[Fluff]

How about

Dim myNewSheetName As String
myNewSheetName = Lookup.Range("A4").Value
If myNewSheetName = "" Then
   MsgBox "Sheet name cannot be blank"
   Exit Sub
ElseIf Evaluate("isref('" & myNewSheetName & "'!A1)") Then
   MsgBox myNewSheetName & " is already taken"
   Exit Sub
End If
Template.Copy , Sheets("Template")
ActiveSheet.Name = myNewSheetName

In future please post you code not an image How to Post Your VBA Code

 

[deanw55]

Thank you Fluff for your answer and suggestion. I'm trying to use your code, but somehow don't know how to place it right into my piece of code to get it working. I assume I also have some unusable lines in my existing code

Sub CreateNewSheet()

    Application.ScreenUpdating = False
    Sheets("TEMPLATE").Visible = True
    Set Template = Sheets("TEMPLATE")
    Set Lookup = Sheets("ADD")
    Dim myNewSheetName
    myNewSheetName = ThisWorkbook.Sheets("ADD").Range("D4")
'   DataSheet = Sheets(myNewSheetName)

On Error Resume Next

If myNewSheetName Is Nothing Then
           
    Template.Select
    ActiveSheet.Copy after:=Sheets("TEMPLATE")
    ActiveSheet.Name = myNewSheetName
    
    Lookup.Select
    Range("D4").Copy
    Sheets(myNewSheetName).Activate
    Range("N2").PasteSpecial xlPasteValues
    Lookup.Select
    Range("D3").Copy
    Sheets(myNewSheetName).Activate
    Range("N3").PasteSpecial xlPasteValues
    Lookup.Select
                    
    Application.CutCopyMode = False
    
    Sheets(myNewSheetName).Activate
    Call LookupIp
        
    Sheets("TEMPLATE").Visible = False
    Application.ScreenUpdating = True
    
Else
    MsgBox ("ALERT! Sheet with this name already existing!")
End If
    
    On Error GoTo 0
  
End Sub

 

[deanw55]

Ok, somehow figured it out and it's working as supposed. Now I have next question.
I am creating new sheets in two steps - first step is to create the sheet and name it with the data entered in D4. Then I copy the values from D3 (ID number) and from D4 (Name of a location), both entered by users, into the new worksheet in cells N2 and N3. But because users could name the location different (in some cases with abbreviations) I could have two sheets for same location with different names - but the ID of the location is exact and cannot change. So I would like to be able to lookup the ID number in the existing sheets (cell N3) and if it exists, tell the user about it and change to that sheet. Else the new sheet with the name D4 should be created. Does it make sense?

 

[Fluff]

As this is a totally different question, you need to start a new thread for it. Thanks

 

[deanw55]

Ok, thank you. I'll start a new thread. I was assuming that I could somehow extend the existing vba code. Thank you.
BR, Dean

 

[Fluff]

Glad to help & thanks for the feedback.