I am trying to write a simple VBA program for a spreadsheet. However
I
am stuck. I am learning this as I go along so that is the big problem
no doubt. I need to copy a sheet and rename. Simple eh? I copy it no
problem but cannot figure how to change the copyed name (now
firstname(2) for instance) to a new name. A cut out of the utility is
below:

Private Sub copysht()
sname = "dog"
Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
'.name
= "sname" 'copy at the end
Sheets("Template(2)").name = "sname"
'ActiveWorkbook.Sheets("Template(2)").name = "sname" 'give sheet a
new
name but it blows up on Template(2)!
End Sub

Note that the copied sheet name Template (2) has a space.

Add that to your macro.

Private Sub copysht()
sname = "dog"
Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
Sheets("Template (2)").Name = sname
End Sub


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:18:13 -0800 (PST), wrote:

I am trying to write a simple VBA program for a spreadsheet. However
I
am stuck. I am learning this as I go along so that is the big problem
no doubt. I need to copy a sheet and rename. Simple eh? I copy it no
problem but cannot figure how to change the copyed name (now
firstname(2) for instance) to a new name. A cut out of the utility is
below:

Private Sub copysht()
sname = "dog"
Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
'.name
= "sname" 'copy at the end
Sheets("Template(2)").name = "sname"
'ActiveWorkbook.Sheets("Template(2)").name = "sname" 'give sheet a
new
name but it blows up on Template(2)!
End Sub

On Mar 4, 8:57*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Note that the copied sheet name Template (2) has a space.

Add that to your macro.

Private Sub copysht()
* * sname = "dog"
* * Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
* * Sheets("Template (2)").Name = sname
End Sub

Gord Dibben *MS Excel MVP



On Tue, 4 Mar 2008 12:18:13 -0800 (PST), wrote:
I am trying to write a simple VBA program for a spreadsheet. However
I
am stuck. I am learning this as I go along so that is the big problem
no doubt. I need to copy a sheet and rename. Simple eh? I copy it no
problem but cannot figure how to change the copyed name (now
firstname(2) for instance) to a new name. A cut out of the utility is
below:

Private Sub copysht()
* *sname = "dog"
* *Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
'.name
= "sname" 'copy at the end
* *Sheets("Template(2)").name = "sname"
'ActiveWorkbook.Sheets("Template(2)").name = "sname" 'give sheet a
new
name but it blows up on Template(2)!
End Sub- Hide quoted text -

- Show quoted text -

Bloody Hell! You're right! Main I worked all day trying every combo
possible and never thought there was a space there. Thanks very much!!

Wayne

New eyes.........new view<g


Gord

On Tue, 4 Mar 2008 14:07:21 -0800 (PST), wrote:

Bloody Hell! You're right! Main I worked all day trying every combo
possible and never thought there was a space there. Thanks very much!!

Wayne