Dynamic Sheet Tab Naming

[rpgun]

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

 

[Bob Phillips]

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.