Try this version:

Sub update_all_names2()
dim sh as worksheet
For Each sh In ActiveWorkbook.workSheets
sh.Name = sh.Cells(2, 1).Value
Next sh
End Sub

There's a difference between Sheets and Worksheets. If you have a chartsheet in
your workbook, then that older version would fail.

In fact, a little error checking is usually a nice thing:

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Cells(2, 1).Value
If Err.Number <> 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
On Error GoTo 0
Next sh
End Sub



BitsofColour wrote:
>
> Ok, now I get run-time error '91' - object variable or with block variable
> not set
>
> The data I need the tab to mimick is actually in cell B1....so I changed the
> cell to (1, 2) and still get the same error message....it doesn't appear to
> agree with the "For Each sh In ActiveWorkbook.Sheets" part of the macro
> now.....
>
> Thanks for your help!
>
> "Dave Peterson" wrote:
>
> > Try this:
> >
> > sh.Name = sh.Cells(2, 1).Value
> >
> > But you should have a nice name in A2 of all the sheets--it has to be valid and
> > can't already be used.
> >
> > BitsofColour wrote:
> > >
> > > Paul,
> > > I copied your macro below and keep receiving a "debug" error....for some
> > > reason its not liking tis part of the macro: sh.Name = Cells(2, 1).Value
> > >
> > > Any suggestions???
> > > Thanks!
> > >
> > > "Paul Sheppard" wrote:
> > >
> > > >
> > > > TJ Wrote:
> > > > > I create many worksheets for accounts that have the same format. I name
> > > > > the
> > > > > worksheets by the account number that corresponds to the worksheet.
> > > > > How can
> > > > > I automate the naming of the worksheet tabs? I am unable to put any
> > > > > type of
> > > > > formula in the worksheet tab that would reference the account numbers.
> > > >
> > > > TJ
> > > >
> > > > The following Macro will rename the sheet tabs to whatever is in cell
> > > > A1, just adjust it to the cell where your Account Number is and it will
> > > > rename the tabs to be the same as the account number
> > > >
> > > > Sub update_all_names()
> > > > For Each sh In ActiveWorkbook.Sheets
> > > > sh.Activate
> > > > sh.Name = Cells(1, 1).Value <---(Cells (1,1) = A1, change as required
> > > > Next sh
> > > > End Sub
> > > >
> > > >
> > > > --
> > > > Paul Sheppard
> > > > ------------------------------------------------------------------------
> > > > Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=392169
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson