zendog1960
Active Member
- Joined
- Sep 27, 2003
- Messages
- 459
- Office Version
- 2019
- Platform
- Windows
here is the code I have now...
This does what I want except it renames the "Template" sheet to "Template (2)" How can I right the code to preserve the name "Template" so it will continue to work? Once the name changes to Template (2), this code no longer works because it is looking for "Template" and cannot find it.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Copy_Sheet
End Sub
Sub Copy_Sheet()
Dim wSht As Worksheet
Dim shtName As String
shtName = Sheets("Location Summary").Range("J11")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Make necessary " & _
"corrections and try again."
Exit Sub
End If
Next wSht
Sheets("Template").Copy After:=Sheets("Coin Count")
Sheets("Template").Name = shtName
Sheets(shtName).Move After:=Sheets("Location Summary")
Sheets(shtName).Range("A1") = shtName
End Sub
This does what I want except it renames the "Template" sheet to "Template (2)" How can I right the code to preserve the name "Template" so it will continue to work? Once the name changes to Template (2), this code no longer works because it is looking for "Template" and cannot find it.