John,

Yeah, that was a rather crucial line of code. I'm glad you got it
working.


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



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