VBA Copies but needs to save name

[zendog1960]

here is the code I have now...

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.

 

[Norie]

Well for a start you can't have 2 sheets called Template in the same workbook.

So if you aren't deleting or renaming the original then you can't do what you want.

 

[zendog1960]

Here's the fix I came up with

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
Sheets("Template (2)").Name = ("Template")
Sheets("Location Summary").Activate
End Sub

Now one to the next problem I want to tackle. Once the new sheet is created, is there a way to populate the Location Summary on the next available blank line with the cell references in the correct places? Here is the Location Summary Pages....

Coin Shooter.xls
	A	B	C	D	E	F
2	Park	Time	Total Coins	Total Value	Total PCV	Total VPH
3	Example Park	2.50	4	$3.56	$0.05	$1.42
4
5
6
7
8
9
Location Summary

 

[zendog1960]

what I would like is have the new sheet in this case populate the columns A thru F on row 4. When the next new sheet is created, it would populate row 5 with the same information except with the date from the newly created sheet.