Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy many sheets into one twist Andy Excel Discussion (Misc queries) 2 March 6th 09 02:42 AM
Copy a Formula down with a Twist Q John Excel Worksheet Functions 7 November 23rd 04 11:03 PM
copy range on every worksheet (diff names) to a master worksheet (to be created) Bernie[_2_] Excel Programming 2 September 22nd 04 03:30 PM
Re-Name a Worksheet..... with a Twist John[_81_] Excel Programming 18 April 21st 04 07:27 AM
Code to copy range vs Copy Entire Worksheet - can't figure it out Mike Taylor Excel Programming 1 April 15th 04 08:34 PM


All times are GMT +1. The time now is 07:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"