Thanks Gary, have run into a further problem -
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 i.e.
=Master!C6

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.

I do want the tab names to change as a result of the master sheet sort,
because the individual sheets take their info from the master sheet too...can
I email you the file as an attachment?

Any ideas? Am now off to bed as nearly midnight. Thank you so much for
your support - I am learning so much and really appreciate the time you are
taking to help me resolve this!


"GaryDK" wrote:

Hi Denise,

The simplest way to handle that, and a better way, is to make your name
cells on each sheet named ranges (Insert | Name | Define). For example,
if the cell is named "DataSheet" on the sheet, you could enter the
following in its module:

Option Explicit

Private Sub Worksheet_Calculate()
On Error GoTo ErrorTrap
If Range("DataSheet").Value < Me.Name Then
Me.Name = Range("DataSheet").Value
End If
Exit Sub
ErrorTrap:
MsgBox "New sheet name in range DataSheet " & _
"contains invalid characters."
End Sub

Then you can enter the same code in another sheet's module, replacing
"DataSheet" with whatever the defined name is for that particular
sheet's "name cell". Great catch on the illegal characters, so it now
traps errors. There could be other errors, like the range name not
existing, or misspelling the name in the code, but I assume you'd catch
that in testing.

Gary