No dice Chip, stilll renames as before

"Chip Pearson" wrote in message
...
Try

Dim SHName As String
SHName = Worksheets("Template").Range("AA1").Text
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(SHName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
With Worksheets
Worksheets("Template").Copy after:=.Item(.Count)
End With
ActiveSheet.Name = SHName



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"John" wrote in message
...
Thanks Chip, not quite what I want. It copies, but the NewSheet does't
rename as per text in AA1 (It actual re-names as the Template sheet name
(1). Also if a NewSheet exists with name that exists in AA1, it just
creates another additional sheet.


"Chip Pearson" wrote in message
...
John,

Try the following:

Dim SHName As String
SHName = ActiveSheet.Range("AA1").Text
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(SHName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
With Worksheets
ActiveSheet.Copy after:=.Item(.Count)
End With
ActiveSheet.Name = SHName


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"John" wrote in message
...
How would I achieve the following

1) Copy a set template worksheet to a new sheet (retaining formats etc,
but with absolute values)

2) Rename this new sheet as per the value that's, say within AA1 of
Template sheet. This value will be a date, so I wish the sheet name to
appear dd-mm-yy

3) If the new sheet name already exists, just overwrite this sheet
regardless with the new values.

I am trying to build up a history of info per week

Thanks