Copy template worksheet, rename it only if the name is not already taken

AlexisChch

New Member
Joined
Jul 3, 2022
Messages
3
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hi all,

I'm new to this forum.

It seems simple but I've searched a lot online and couldn't find something that works. I searched a lot in this forum and I found lot of suggestions but nothing worked for my case.

I'm trying to copy a template worksheet (called "Template") and rename it using a command button. I have managed to achieve this.
However, I would like to rename the new worksheet only if the name is not already exists.

If the name of the worksheet exists already in another worksheet I would like to
  1. get a pop up message saying something like this: "Entry 01 already exists, please exit and insert Entry 02" and then
  2. I would like the code to prevent from creating a copy of the "Template".

Right now I have this code:

VBA Code:
Private Sub cmdSV1_Click()
With Sheets("Front Sheet")
    If .Range("B17") = "" Then
        MsgBox "Please enter the Site Visit reference number in cell B17 before you 'Insert the Entry'"
        .Activate
        .Range("B17").Select
       Exit Sub
    End If
End With
Sheets("Template").Copy After:=Sheets("Front Sheet")
ActiveSheet.Name = Sheets("Front Sheet").Range("B17")
End Sub

This code works exactly as I want. What it does is:
  • Creates a new worksheet based on a copy of the "Template" worksheet
  • rename the worksheet to what is in range("B17") of the "Front Sheet" Worksheet (Range B17 is always the number "1").
When I click the command button for the first time it works great and does what I want. However, once I click the same command button for the second time I get the 1004 error. "That name is already taken. Try a different one. End, Debug." Then the code creates a new worksheet with the name "Template (2)".

I would like when I click the command button to get a pop up message if the worksheet name already exists in the spreadsheet.

*PS. The "With ..End With" part of the code above works as I want but is irrelevant to my question. I put it there so I can get a pop up message when a user is trying to insert a new entry without filling the "B17" cell from the "Front Sheet".

Any help would be much appreciated.
Many thanks
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

AlexisChch

New Member
Joined
Jul 3, 2022
Messages
3
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Update
I managed to find my way around. Perhaps other people may find this helpful.

VBA Code:
Private Sub cmdSV1_Click()
Dim strName As String
  strName = Sheets("Front Sheet").Range("B17")
    For Each Worksheet In Worksheets
    If Worksheet.Name = "1" Then
      MsgBox "An existing tab is named " & strName & "." _
      & vbCrLf & "Please insert the correct site visit entry"
      Exit Sub
    End If
  Next
With Sheets("Front Sheet")
    If .Range("B17") = "" Then
        MsgBox "Please fill in a Site Visit reference number(cell B17) before you 'Insert a Site Visit Entry'"
        .Activate
        .Range("B17").Select
       Exit Sub
    End If
    End With
Sheets("Template").Copy After:=Sheets("Front Sheet")
ActiveSheet.Name = Sheets("Front Sheet").Range("B17")
End Sub

The above works perfectly.
However, I now need to find a way where I can copy the "Template" worksheet when it is hidden. The above code works only when the "Template" worksheet is unhidden.

If I hide the "Template" worksheet and click the button to copy the "Template" worksheet I will get a new worksheet (named "1") but then my "Front Sheet" will disappear.

Any idea would be much appreciated.
 

AlexisChch

New Member
Joined
Jul 3, 2022
Messages
3
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
I'm sorry for the repetitive posts.

I couldn't find an option to edit my original post above and hence I have three posts in a row.

I find the solution myself and the code I used it does the job for me.

VBA Code:
Private Sub cmdSV1_Click()
Dim strName As String
  strName = Sheets("Front Sheet").Range("B17")
    For Each Worksheet In Worksheets
    If Worksheet.Name = "1" Then
      MsgBox "An existing tab is named " & strName & "." _
      & vbCrLf & "Please insert the correct site visit entry"
      Exit Sub
    End If
  Next
With Sheets("Front Sheet")
    If .Range("B17") = "" Then
        MsgBox "Please fill in a Site Visit reference number(cell B17) before you 'Insert a Site Visit Entry'"
        .Activate
        .Range("B17").Select
       Exit Sub
    End If
    Worksheets("Template").Visible = xlSheetVisible
    End With
Sheets("Template").Copy After:=Sheets("Front Sheet")
ActiveSheet.Name = Sheets("Front Sheet").Range("B17")
Worksheets("Template").Visible = xlSheetHidden
End Sub

Many thanks
 

Forum statistics

Threads
1,175,670
Messages
5,898,811
Members
434,731
Latest member
njakfla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top