VBA Code to Duplicate Sheet - Duplicate Sheet Name Error

[dakiav]

Hello! This is my first post to this forum. As the saying goes, I know enough to be dangerous, but now I need some help beyond my current knowledge.

I have a workbook that contains numerous worksheets, one each for different cities, and each worksheet name is based on the value in a named cell, "City_Name".

I am using the VBA code (below) to automatically name the worksheet tab the same value as the referenced cell, "City_Name". The code is located in each sheet as a Microsoft Excel Object.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
ActiveSheet.Name = Range("City_Name").Value
End Sub

However, here's is one problem I can't find a solution to. When I copy an existing sheet to create a duplicate, I get a "Run-time error '1004': That name is already taken. Try a different one."

I know the error is happening because the duplicate sheet, is of course, duplicating everything form the original sheet, including the value in the named cell, "City_Name". So the minute the duplicate sheet is created, the VBA code runs to rename the sheet, but it can't because it would be a duplicate name.

So, (finally!), here's my question - Is there a way duplicate a sheet and pause the VBA code until the sheet is renamed?, then have the VBA code run as normal, and still as an Excel Object?

I know there are methods for running a macro to copy a sheet and rename it, but I haven't found a way to keep a constant link to the the cell value, the way the above code works. This constant linkage makes this code so handy.
Thanks in advance for any suggestions!

 

[portews]

I would suggest you launch an Input box at the beginning of the code to provide the new City_Name value, copy the existing sheet to a new sheet with that name, then you can automatically put the name in your "City_Name" cell. Another option which would be to create the page with a unique name (e.g. "Template") then rename it once the new name is put in the "City_State" cell.

You could, theoretically put a function in the "City_Name" cell that would make that equal to the tab name and make the tab name the driver, rather than the cell.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

 

[dakiav]

Thank you Bill. I appreciate your response! I tried launching an Input Box but am not having success. The best I can do is get the Input Box to execute, but then I still get a Run-time error.

The code I created is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Range("City_Name").Select
Dim myValue As Variant
myValue = InputBox("Enter City Name")
Range("City_Name").Value = myValue

ActiveSheet.Name = Range("City_Name").Value

End Sub

I also tried creating the IB by writing the code as its own Sub. Still no luck. Can you see anything that could be changes to correct the problem?

As for giving the page a unique name (e.g. "Template"), the only way I know of to rename it is to do it manually or by using the formula you provided. The problem with that is a specific sheet would have to be copied (e.g. "Template"). My goal is to enable anyone who uses this sheet to "simply" copy/paste any existing sheet and keep the automatic Cell Value to Tab Name function in tact. Maybe "simply" isn't possible.

 

[portews]

When I gave my suggestion, I thought (wrongly) that you could rename during the copy. The program will, however, append a (2) if the name already exists, so having a duplicate shouldn't cause an error during the copy.

You have the code in the Worksheet_SelectionChange routine, so it looks like anytime you click in a different cell on that sheet, it should spawn a new worksheet. Is this what you want? I would suggest a separate routine (like below) and have people run it, or put a button on the page to run it. We can restrict that to one or a couple of cells by checking for the target, if you like. Let me know.

Sub CopySheet()
Dim myValue As Variant
Dim x As String
Dim i As Long
'message box to get the name
myValue = InputBox("Enter City Name")
'in case of cancel
If myValue = "" Then End
'check through all the sheets to see if that name is taken
For i = 1 To ThisWorkbook.Sheets.Count
    'if there's a match...
    If myValue = Sheets(i).Name Then
        'pop a message box...
        MsgBox ("Name already exists")
        'and quit
        End
    End If
Next
'otherwise, copy the sheet your on and put it at the end
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
'rename the sheet
ActiveSheet.Name = myValue 'Set new name
'put the name in the City_Name range.
Range("City_Name").Value = myValue
'... and Bob's your uncle
End Sub

 

[dakiav]

Good day and thank you for your reply!

I tried the code and it worked, but it also deleted the formula in the named cell, (City_Name) of the source/master worksheet. So while it worked for the first copy of the sheet, it did not work for any subsequent copies because the formula was deleted. So, all I had to do was figure out how to replace the code in the source (master) worksheet, which I did below.

Using your code, I will modify to target a Master worksheet (a template) that when copied, the copy will be placed as the last worksheet in the workbook. Perhapes the code I added can be condensed even more, but it works so that's good enough for me. I will hopefully be able to execute this copy command while the worksheet is hidden. If not, I'll just add the code to unhide the sheet, execute the copy command then hide it again.

Ultimately, I can make this work but in a perfect world, I'd still love to find a way to have the tab name pull from the cell value. But sometimes, the world isn't perfect.

Again, thank you so much for helping solve this!

Sub CopySheet()
Dim myValue As Variant
Dim x As String
Dim i As Long
'message box to get the name
myValue = InputBox("Enter City Name")
'in case of cancel
If myValue = "" Then End
'check through all the sheets to see if that name is taken
For i = 1 To ThisWorkbook.Sheets.Count
    'if there's a match...
    If myValue = Sheets(i).Name Then
        'pop a message box...
        MsgBox ("Name already exists")
        'and quit
        End
    End If
Next
'otherwise, copy the sheet your on and put it at the end
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
'rename the sheet
ActiveSheet.Name = myValue 'Set new name
'put the name in the City_Name range.
Range("City_Name").Value = myValue
'... and Bob's your uncle
'
'''Added the following to repopulate the Tab-to-Cell name formula in the master worksheet.
Sheets("Master").Select
Range("City_Name").Formula2R1C1 = _
        "=MID(CELL(""filename"",R[-9]C),FIND(""]"",CELL(""filename"",R[-9]C))+1,255)"
       
'''Added this so that Cell A1 is selected in the newly added worksheet.
Sheets(Sheets.Count).Activate
ActiveSheet.Range("A1").Select
'... and Jane's your aunt! :-)

End Sub

 

[dmt32]

I will modify to target a Master worksheet (a template) that when copied, the copy will be placed as the last worksheet in the workbook.

Ultimately, I can make this work but in a perfect world, I'd still love to find a way to have the tab name pull from the cell value.

Hi,
see if this update to your code does what you want

Sub CopySheet()
    Dim sheetname   As Variant
    Dim strDefault  As String
    
    On Error GoTo myerror
    
    sheetname = Range("City_Name").Value
    If Len(sheetname) = 0 Then Exit Sub
    
    strDefault = sheetname
    
    Do While Evaluate("ISREF('" & sheetname & "'!A1)")
        sheetname = InputBox("Sheet " & sheetname & " Already Exists" & Chr(10) & _
                             "Please Enter A New Sheet Name", "Sheet Name", strDefault)
        'cancel pressed
        If StrPtr(sheetname) = 0 Then Exit Sub
        'blank entry
        If Len(sheetname) = 0 Then sheetname = strDefault
    Loop
    
    Worksheets("Template").Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = sheetname

myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

Change the Template sheet name shown in BOLD as required.

Dave

 

[dakiav]

Thank you Dave!

This is an interesting solution - interesting in a good way. I like the fact that with this code, it confirms at the very start that the name already exists and shows the name. And if you try to OK when the name field is empty, it doesn't completely close the procedure and instead, leaves the Insert Name box open.

Between this and what Bill previously sent, you have each provided two great solutions.

Now, if I could just get the Sheet Name to feed from the Cell value. I know, Unicorn!

 

[portews]

Now, if I could just get the Sheet Name to feed from the Cell value.

change the ActiveSheet.Name code in either my or dmt32's code to:

ActiveSheet.Name = Range("City_Name").Value

 

[dmt32]

Now, if I could just get the Sheet Name to feed from the Cell value. I know, Unicorn!

my solution does take sheetname from the cell value?

sheetname = Range("City_Name").Value

this is only changed at the inputbox if sheet name exists

change the ActiveSheet.Name code in either my or dmt32's code to:

ActiveSheet.Name = Range("City_Name").Value

Your suggested change to my code would defeat an entry made by user at the inputbox if it is needed.

Dave

 

[dakiav]

I've finally been able to get back into this project. Thank you both for your help!

I have settled on your code dmt32.

Sub CopySheet()
Dim sheetname As Variant
Dim strDefault As String

On Error GoTo myerror

sheetname = Range("City_Name").Value
If Len(sheetname) = 0 Then Exit Sub

strDefault = sheetname

Do While Evaluate("ISREF('" & sheetname & "'!A1)")
sheetname = InputBox("Sheet " & sheetname & " Already Exists" & Chr(10) & _
"Please Enter A New Sheet Name", "Sheet Name", strDefault)
'cancel pressed
If StrPtr(sheetname) = 0 Then Exit Sub
'blank entry
If Len(sheetname) = 0 Then sheetname = strDefault
Loop

Worksheets("Template").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = sheetname

myerror:
If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"

End Sub

I've included this with various other macros to achieve my final solution. I have a lot of different macros in the workbook - probably not written in the most efficient manner, but in the end, it achieves my needs.

Thank you both again! I hope some day I too, will have enough knowledge to pay it forward to others.

Dakiav