naming tab macro error runtime error 1004

[D]

Gary DK very kindly provided a solution for naming tabs from the contents of
a cell on a worksheet. A further problem has arisen:

The contents of cell B7 on all 125 sheets provides the name for the sheet
tab. Cell B7 gets its information from a separate master sheet.

When I sort the master sheet (because new entries have been added at the
bottom and need to be arranged according to a ref. code) I get an error
message: Runtime error 1004 Cannot rename a sheet to the same name as another
sheet, a referenced object library or a workbook referenced by Visual Basic.

this is the code:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value <> shname Then
shname = [b7].Value
End If
Me.Name = shname
End Sub


Any ideas?

 

[Otto Moehrbach]

Does B7 of each sheet have a formula that links back to the Master sheet?
If so, your problem is that each of these formulas links back to a specific
cell in the Master sheet. When you sort the Master sheet list, each formula
will calculate and try to name the Me sheet the new name that is now in the
linked cell.
Not knowing everything you are doing, it is difficult to give advice.
You might try adding code to your macro to delete the formula once the sheet
is named. Also add code to not rename the sheet if B7 is blank. HTH Otto

 

[D]

Hi, Otto

Thanks for that - yes, B7 has a formula e.g. =MASTER!C6. I do want the
sheet tab to rename when I sort as the sheet content will change to reflect
the new order in the master sheet.

Is it possible to email you the file? If not, then let me know and I will
try and describe more fully.

Many, many thanks for your time and expertise!

D

Does B7 of each sheet have a formula that links back to the Master sheet?
If so, your problem is that each of these formulas links back to a specific
cell in the Master sheet. When you sort the Master sheet list, each formula
will calculate and try to name the Me sheet the new name that is now in the
linked cell.
Not knowing everything you are doing, it is difficult to give advice.
You might try adding code to your macro to delete the formula once the sheet
is named. Also add code to not rename the sheet if B7 is blank. HTH Otto

Gary DK very kindly provided a solution for naming tabs from the contents
of
a cell on a worksheet. A further problem has arisen:

The contents of cell B7 on all 125 sheets provides the name for the sheet
tab. Cell B7 gets its information from a separate master sheet.

When I sort the master sheet (because new entries have been added at the
bottom and need to be arranged according to a ref. code) I get an error
message: Runtime error 1004 Cannot rename a sheet to the same name as
another
sheet, a referenced object library or a workbook referenced by Visual
Basic.

this is the code:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value <> shname Then
shname = [b7].Value
End If
Me.Name = shname
End Sub


Any ideas?

 

[Otto Moehrbach]

D
Yes, email the file and a more detailed explanation of what you want.
If your file has sheets other than the Master and the sheets with the name
changes, point those out to me. My email address is (e-mail address removed).
Remove the "nop" from this address. Otto

Hi, Otto

Thanks for that - yes, B7 has a formula e.g. =MASTER!C6. I do want the
sheet tab to rename when I sort as the sheet content will change to
reflect
the new order in the master sheet.

Is it possible to email you the file? If not, then let me know and I will
try and describe more fully.

Many, many thanks for your time and expertise!

D

Does B7 of each sheet have a formula that links back to the Master sheet?
If so, your problem is that each of these formulas links back to a
specific
cell in the Master sheet. When you sort the Master sheet list, each
formula
will calculate and try to name the Me sheet the new name that is now in
the
linked cell.
Not knowing everything you are doing, it is difficult to give advice.
You might try adding code to your macro to delete the formula once the
sheet
is named. Also add code to not rename the sheet if B7 is blank. HTH
Otto

Gary DK very kindly provided a solution for naming tabs from the
contents
of
a cell on a worksheet. A further problem has arisen:

The contents of cell B7 on all 125 sheets provides the name for the
sheet
tab. Cell B7 gets its information from a separate master sheet.

When I sort the master sheet (because new entries have been added at
the
bottom and need to be arranged according to a ref. code) I get an error
message: Runtime error 1004 Cannot rename a sheet to the same name as
another
sheet, a referenced object library or a workbook referenced by Visual
Basic.

this is the code:

Option Explicit
Private shname As String

Private Sub Worksheet_Calculate()
If Range("B7").Value <> shname Then
shname = [b7].Value
End If
Me.Name = shname
End Sub


Any ideas?