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.
TJOriginally Posted by 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,
I have the same scenario however, I have established a list of the account
numbers on a seperate sheet. How can I refer back to that sheet using your
macro?
"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
>
>
[QUOTE=MarcusA]Paul,
I have the same scenario however, I have established a list of the account
numbers on a seperate sheet. How can I refer back to that sheet using your
macro?
Hi Marcus
Try this, sorry it is not my code so I cannot explain how it works
Sub namesheets()
Dim arr As Variant
arr = Range("a2:a10").Value
For i = LBound(arr) To UBound(arr)
Sheets(i + 1).Activate
Sheets(i).Name = arr(i, 1)
Next i
End Sub
You will need to change the range to suit your data, line 3 currently a2:a10
this will name the first 9 sheets in the workbook, so if you dont want the worksheet with your account numbers renamed make sure it is to the right of those you do want to rename
Save your file with a different name and have a play
Paul
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
>
>
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
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
>
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks