Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy A Worksheet, with a Twist
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy many sheets into one twist | Excel Discussion (Misc queries) | |||
Copy a Formula down with a Twist Q | Excel Worksheet Functions | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Re-Name a Worksheet..... with a Twist | Excel Programming | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming |