Auto renaming sheet, warning if name exists

[miicker]

I am currently using the following code:

Sub CopyTable()    Sheets("Brongegevens").Select
    Sheets("Brongegevens").Copy After:=Sheets(1)
    ActiveSheet.Name = Range("CodeCopyTabblad").Value
    End Sub

This code copies a worksheet, and then renames it to whatever value is in the named range "CodeCopyTabblad".
This works fine, except when the name already exists, I get a VBA error.

What I would like to happen is a user friendly error, which says something like
"This worksheet already exists, please choose a different name or delete the old worksheet first"

Optionally would be an option to overwrite the worksheet, and/or present an input field where the user can type the new name.
I'm not that good in VBA, so I have no idea where to start.

Thanks in advance.

 

[My Aswer Is This]

Try this:

Sub CopyTable()
On Error GoTo M
Sheets("Brongegevens").Select
    Sheets("Brongegevens").Copy After:=Sheets(1)
    ActiveSheet.Name = Range("CodeCopyTabblad").Value
    Exit Sub
M:
    MsgBox "Sheets named " & Range("CodeCopyTabblad").Value & " already exist"
    ActiveSheet.Delete
    End Sub

 

[miicker]

Thanks for the response.

This works a bit, it asks me to delete the worksheet after pressing OK. But when I delete it, it also clears the sheet that had the same name for some reason. When I hit cancel and delete the sheet manually, the sheet which had the same name is not cleared. I'm now trying a different approach which i've found on the internet:

Sub CopyTable()On Error GoTo M
Sheets("Brongegevens").Select
    Sheets("Brongegevens").Copy After:=Sheets(1)
    ActiveSheet.Name = Range("CodeCopyTabblad").Value
    
M:
    Call sheetExists
    If sheetExists = True Then MsgBox "Some error message"
Exit Sub
End Sub


Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each Sheet In Worksheets
        If sheetToFind = Range("CodeCopyTabblad").Value Then
            sheetExists = True
            Exit Function
        End If
    Next Sheet
End Function

I don't know how to proparly use the function within the sub.

 

[My Aswer Is This]

Try this:

Sub CopyTable()
On Error GoTo M
Sheets("Brongegevens").Select
    Sheets("Brongegevens").Copy After:=Sheets(1)
    ActiveSheet.Name = Range("CodeCopyTabblad").Value
    Exit Sub
M:
    MsgBox "Sheets named " & Range("CodeCopyTabblad").Value & " already exist" & vbNewLine & "  Or some other thing went wrong"
    
    End Sub

 

[JackDanIce]

Also try below, which tests if the sheet already exists and notifies if it does, else it makes a copy and renames the sheet:

Sub CopyTable()
    
    Dim wks As Worksheet
    
    With Range("CodeCopyTabllad")
        On Error Resume Next
        Set wks = Sheets(.Value)
        On Error GoTo 0
    
        If Not wks Is Nothing Then
            MsgBox "Sheet with name: " & .Value & vbCrLf & vbCrLf & "Already exists!", vbExclamation, "Sheet Already Exists"
            Set wks = Nothing
        Else
            Sheets("Brongegevens").Copy after:=Sheets(1)
            ActiveSheet.Name = .Value
        End If
    End With
    
End Sub

 

[steve the fish]

This is how i would go about using that function within your macro:

Sub CopyTable()

If sheetExists(Range("CodeCopyTabblad").Value) = True Then
    MsgBox "Some error message"
    Exit Sub
Else
    Sheets("Brongegevens").Copy After:=Sheets(1)
    ActiveSheet.Name = Range("CodeCopyTabblad").Value
End If

End Sub

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each Sheet In Worksheets
        If UCase(Sheet.Name) = UCase(Range("CodeCopyTabblad").Value) Then
            sheetExists = True
            Exit Function
        End If
    Next Sheet
End Function

 

[JackDanIce]

@steve the fish: why loop through every sheet object rather than set a variable = to sheet with that name? If the variable is not nothing then the sheet exists else it doesn't. Would possibly be quicker, the greater the number of sheets that exist? Admittedly, it doesn't consider the case of the string:

Function sheetExists(sheetToFind As String) As Boolean
    Dim wks As Worksheet
    
    On Error Resume Next
    Set wks = Sheets(sheetToFind)
    On Error GoTo 0
    
    sheetExists = (Not wks Is Nothing)
    
End Function

Also, initial value of a boolean function is FALSE so no need to declare that before the rest of the code runs

 

[My Aswer Is This]

The script I provided in Post 2 works. But the user had some other type of error so it deleted the active sheet before attempting to make the new sheet.

 

[steve the fish]

Hi jack. I did that just because the OP wanted to know how to incorporate his function in a macro.

 

[JackDanIce]

This script replaces existing sheet or creates a new sheet, depending on user choice:

Sub CopyTable()
    
    Dim wks As Worksheet
    Dim msg As String
    
    With Range("CodeCopyTabllad")
        On Error Resume Next
        Set wks = Sheets(.Value)
        On Error GoTo 0
    
        If Not wks Is Nothing Then
        
            msg = "Sheet with name: @VALUE@1@1Already exists!@1@!Click OK to delete old sheet and replace with copy@1@1Or Cancel to stop macro"
            msg = Replace(msg,  @VALUE", .Value)
            msg = Replace(msg, "@1", vbCrLf)
            
            If MsgBox(msg, vbOKCancel, "Sheet Already Exists") = vbOK Then
                Application.DisplayAlerts = False
                wks.Delete
                Application.DisplayAlerts = False
            Else: Exit Sub
            End If
            
        End If
        Sheets("Brongegevens").Copy after:=Sheets(1)
        ActiveSheet.Name = .Value
        
    End With
    
End Sub