Chip, my error, I removed the rather critical line "ActiveSheet.Name =
SHName" from my code

It works great now. I just formated my date with dd-mm-yy

Thanks for your help

"Chip Pearson" wrote in message
...
I should have added that you cannot have a '/' character in a sheet name.
You'll need to get rid of the / characters or choose a different name for
the new sheet.


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


"Chip Pearson" wrote in message
...
If you changed the worksheet name and the cell reference, you can't
expect the code to run as written. Try

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


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







"John" wrote in message
...
Chip, your spot on it will do just that with a blank workbook, but when
I work from my existing workbook and change Template to "Safe Count" and
AA1 to AL1 and run whats below, new sheets are referenced as "Safe
Counts (1)" , next one will be "Safe Counts (2)" etc

My value in AL1 is a formula that Returns the date 23/10/05, not sure if
thats the problem


Sub CopySheet()

Dim SHName As String
SHName = Worksheets("Safe Count").Range("AL1").Text
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(SHName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
With Worksheets
Worksheets("Safe Count").Copy after:=.Item(.Count)
End With
Sheets("Safe Count").Select
Range("G13").Select

End Sub


"Chip Pearson" wrote in message
...
The code I posted will copy the sheet named Template to a new sheet,
whose name is in cell AA1 on the Template worksheet. Any existing
worksheet with this name will be deleted before the Copy operation.

Start with an empty workbook, name one of the sheets 'Template' and put
a value on that sheet in AA1. Now, run the code. You'll have two
worksheets, the original sheet 'Template' and a new sheet, whose name
is in Template!AA1.

Is this not what you are asking for?


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





"John" wrote in message
...
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