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

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

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

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

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

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