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

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" <(E-Mail Removed)>
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

On Sep 7, 12:48 pm, "Jim Cone" <jim.cone...@rcn.comXXX> wrote:
> 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" <seanrya...@yahoo.co.uk>
> 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