Dynamic Sheet Tab Naming
 

Hi folks,

Hope someone can help me out here. I currently use this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If Target.Address < "$A$1" Then Exit Sub
ActiveSheet.Name = Target.Value
End Sub

This dynamically names my sheet tabs with the value in A1. What I need
is a way to copy that sheet...then get in and change the value of A1 o
the copied sheet. (It has to remain in the same workbook.) I get
run-time error when trying to rename "A1" in the copied sheet.

Can one point to a backup secondary name, maybe "A2" to allow a clea
copy so I can rename "A1" ?

Hope that made sense...I appreciate any advice on this.
Thank you.
Bo

--
rpgu
-----------------------------------------------------------------------
rpgun's Profile: http://www.excelforum.com/member.php...fo&userid=1542
View this thread: http://www.excelforum.com/showthread.php?threadid=27030

Dynamic Sheet Tab Naming
 

Bob,

The code should be

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Sh.Name = Target.Value
End Sub

Describe the actions, sequence and input, that give sthis error.

--

HTH

RP

"rpgun" wrote in message
...

Hi folks,

Hope someone can help me out here. I currently use this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address < "$A$1" Then Exit Sub
ActiveSheet.Name = Target.Value
End Sub

This dynamically names my sheet tabs with the value in A1. What I need,
is a way to copy that sheet...then get in and change the value of A1 on
the copied sheet. (It has to remain in the same workbook.) I get a
run-time error when trying to rename "A1" in the copied sheet.

Can one point to a backup secondary name, maybe "A2" to allow a clean
copy so I can rename "A1" ?

Hope that made sense...I appreciate any advice on this.
Thank you.
Bob


--
rpgun
------------------------------------------------------------------------
rpgun's Profile:
http://www.excelforum.com/member.php...o&userid=15426
View this thread: http://www.excelforum.com/showthread...hreadid=270305