naming tab macro error runtime error 1004

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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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

"Otto Moehrbach" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
>
>
>

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 [email protected].
Remove the "nop" from this address. Otto
"D" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Otto Moehrbach" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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?
>> >
>> >
>>
>>
>>