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
>