Copy a Sheet & Rename based on Cell Value Q

[Sean]

I am trying to copy an active sheet to a new sheet and rename based on
the value in A1 (which has a formula that produces a value of ..
"London 07-09-07 12:30", but I'm getting debug on line

ActiveSheet.Name = Range("A1").Value

I guess its the way A1 is formatted, how can I do this?

Thanks


Sub Copy_Rename()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Range("A1").Value
Sheets(shtName).Activate

etc etc

End Sub

 

[Jim Cone]

The colon character ":" is not allowed in a sheet name.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Sean" <[email protected]>
wrote in message
I am trying to copy an active sheet to a new sheet and rename based on
the value in A1 (which has a formula that produces a value of ..
"London 07-09-07 12:30", but I'm getting debug on line

ActiveSheet.Name = Range("A1").Value
I guess its the way A1 is formatted, how can I do this?
Thanks

Sub Copy_Rename()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Range("A1").Value
Sheets(shtName).Activate
etc etc
End Sub

 

[Sean]

The colon character ":" is not allowed in a sheet name.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Sean" <[email protected]>
wrote in message
I am trying to copy an active sheet to a new sheet and rename based on
the value in A1 (which has a formula that produces a value of ..
"London 07-09-07 12:30", but I'm getting debug on line

ActiveSheet.Name = Range("A1").Value
I guess its the way A1 is formatted, how can I do this?
Thanks

Sub Copy_Rename()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Range("A1").Value
Sheets(shtName).Activate
etc etc
End Sub

Thanks Jim, of course