Renaming Tabs

[ANDY73]

I noticed the answer to this question for 3/24/05, however I can't seem to
make it work...

--------- this second option assumes that you have all the sheets in the
workbook and you want to name them as per the list in A2:A10

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

-------

to use either of the above, right mouse click on a sheet tab and choose view
code
this will display the VBE Window
choose insert / module from the menu and you'll get a piece of white paper
on the right hand side of the screen
copy and paste the code above from the word sub to the words end sub into
the right hand side of the screen
press ALT & F11 to return to your workbook
ensure your loan numbers are in cells A2:A10 of the sheet you're viewing and
choose tools / macro / macros from the menu
choose either namesheets or newws as appropriate and click on the run button
hope this helps

What am I doing wrong???? Does it make a difference that I want to start on
Sheet 4, and continue for 40 sheets?

Or the fact that the Cells that I am using are on Sheet 1 ("Record"
AA18:AA57)?

Or lastly, the fact that the information that I want to use is in date format?

[Bob Phillips]

ALl seem relevant

Try

Sub namesheets()
Dim arr As Variant
arr = Range("AA18:AA57").Value
For i = LBound(arr) To UBound(arr)
Sheets(i + 3).Name = Format(arr(i,1),"dd-mmm-yyyy")
Next i
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ANDY73" <[email protected]> wrote in message
news:[email protected]...
> I noticed the answer to this question for 3/24/05, however I can't seem to
> make it work...
>
> --------- this second option assumes that you have all the sheets in the
> workbook and you want to name them as per the list in A2:A10
>
> 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
>
> -------
>
> to use either of the above, right mouse click on a sheet tab and choose
view
> code
> this will display the VBE Window
> choose insert / module from the menu and you'll get a piece of white paper
> on the right hand side of the screen
> copy and paste the code above from the word sub to the words end sub into
> the right hand side of the screen
> press ALT & F11 to return to your workbook
> ensure your loan numbers are in cells A2:A10 of the sheet you're viewing
and
> choose tools / macro / macros from the menu
> choose either namesheets or newws as appropriate and click on the run
button
> hope this helps
>
> What am I doing wrong???? Does it make a difference that I want to start
on
> Sheet 4, and continue for 40 sheets?
>
> Or the fact that the Cells that I am using are on Sheet 1 ("Record"
> AA18:AA57)?
>
> Or lastly, the fact that the information that I want to use is in date
format?
>

[ANDY73]

Thanks for the reply...

Still somewhat confused. Let me lay this out so I don't get confused, let
alone you.
I have a workbook with the following layout
Information sheets - 2 or 3 sheets with infomation for body sheets.
(Not to be renamed, however have the name: (Record, Stats)
or

(Record, Info, and Stats)
Body Sheets - 40 sheets that need renaming.
Chart Sheets - 4 sheets (self-explaining).

The name-change info is located in 'Records' (AA18:AA57)

Do I need to have it as a seperate Macro, or can I add it to an existing one?

Would it make it easier if I put the name-change info in the sheet I want
changed, and then hide the cells? And then have the macro pull the info
there? ( the formula would obviously have to change)

"Bob Phillips" wrote:

> ALl seem relevant
>
> Try
>
> Sub namesheets()
> Dim arr As Variant
> arr = Range("AA18:AA57").Value
> For i = LBound(arr) To UBound(arr)
> Sheets(i + 3).Name = Format(arr(i,1),"dd-mmm-yyyy")
> Next i
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "ANDY73" <[email protected]> wrote in message
> news:[email protected]...
> > I noticed the answer to this question for 3/24/05, however I can't seem to
> > make it work...
> >
> > --------- this second option assumes that you have all the sheets in the
> > workbook and you want to name them as per the list in A2:A10
> >
> > 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
> >
> > -------
> >
> > to use either of the above, right mouse click on a sheet tab and choose
> view
> > code
> > this will display the VBE Window
> > choose insert / module from the menu and you'll get a piece of white paper
> > on the right hand side of the screen
> > copy and paste the code above from the word sub to the words end sub into
> > the right hand side of the screen
> > press ALT & F11 to return to your workbook
> > ensure your loan numbers are in cells A2:A10 of the sheet you're viewing
> and
> > choose tools / macro / macros from the menu
> > choose either namesheets or newws as appropriate and click on the run
> button
> > hope this helps
> >
> > What am I doing wrong???? Does it make a difference that I want to start
> on
> > Sheet 4, and continue for 40 sheets?
> >
> > Or the fact that the Cells that I am using are on Sheet 1 ("Record"
> > AA18:AA57)?
> >
> > Or lastly, the fact that the information that I want to use is in date
> format?
> >
>
>
>

[Bob Phillips]

"ANDY73" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the reply...
>
> Still somewhat confused. Let me lay this out so I don't get confused, let
> alone you.

Did yoiu try it before replying?


> I have a workbook with the following layout
> Information sheets - 2 or 3 sheets with infomation for body sheets.
> (Not to be renamed, however have the name: (Record,
Stats)
> or
>
> (Record, Info, and Stats)
> Body Sheets - 40 sheets that need renaming.
> Chart Sheets - 4 sheets (self-explaining).
>
> The name-change info is located in 'Records' (AA18:AA57)
>
> Do I need to have it as a seperate Macro, or can I add it to an existing
one?

Either, I can't answer which is best without knowing your application.


> Would it make it easier if I put the name-change info in the sheet I want
> changed, and then hide the cells? And then have the macro pull the info
> there? ( the formula would obviously have to change)

Nope. Leave all the data on Records in AA18::AA57.
You kept quiet about the sheets didn't you? I have changed the code to seek
sheet 'Records' and only rename worksheets, not charts

Sub namesheets()
Dim arr As Variant
arr = Worksheets("Records").Range("AA18:AA57").Value
For i = LBound(arr) To UBound(arr)
WorkSheets(i + 3).Name = Format(arr(i,1),"dd-mmm-yyyy")
Next i
End Sub

[ANDY73]

Yeah, it started on sheet 1, and ended with a debug error after page 3
instead of starting on sheet 4 and continuing for the next 40 sheets


"Bob Phillips" wrote:

>
> "ANDY73" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the reply...
> >
> > Still somewhat confused. Let me lay this out so I don't get confused, let
> > alone you.
>
> Did yoiu try it before replying?
>
>
> > I have a workbook with the following layout
> > Information sheets - 2 or 3 sheets with infomation for body sheets.
> > (Not to be renamed, however have the name: (Record,
> Stats)
> > or
> >
> > (Record, Info, and Stats)
> > Body Sheets - 40 sheets that need renaming.
> > Chart Sheets - 4 sheets (self-explaining).
> >
> > The name-change info is located in 'Records' (AA18:AA57)
> >
> > Do I need to have it as a seperate Macro, or can I add it to an existing
> one?
>
> Either, I can't answer which is best without knowing your application.
>
>
> > Would it make it easier if I put the name-change info in the sheet I want
> > changed, and then hide the cells? And then have the macro pull the info
> > there? ( the formula would obviously have to change)
>
> Nope. Leave all the data on Records in AA18::AA57.
> You kept quiet about the sheets didn't you? I have changed the code to seek
> sheet 'Records' and only rename worksheets, not charts
>
> Sub namesheets()
> Dim arr As Variant
> arr = Worksheets("Records").Range("AA18:AA57").Value
> For i = LBound(arr) To UBound(arr)
> WorkSheets(i + 3).Name = Format(arr(i,1),"dd-mmm-yyyy")
> Next i
> End Sub
>
>
>

[ANDY73]

After some additional thought, I was finally able to get your suggestion
working.... I was missing a couple of steps.

I seems to work well, but I came across a new wrinkle. With the formula
that I got from you, the information I get for the rename comes from the 1st
worksheet, hops the next 2 and then renames the remaining 40. My issue is,
it varies on how many sheets are in front of the 40 that need to be renamed.

Is it possible (an easy change) to have the info come from the last sheet,
before the first of 40? (ie. A,B,C(Info), 1-40, Charts) This way it won't
matter how many are in front.

Thanks
"Bob Phillips" wrote:

>
> "ANDY73" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the reply...
> >
> > Still somewhat confused. Let me lay this out so I don't get confused, let
> > alone you.
>
> Did yoiu try it before replying?
>
>
> > I have a workbook with the following layout
> > Information sheets - 2 or 3 sheets with infomation for body sheets.
> > (Not to be renamed, however have the name: (Record,
> Stats)
> > or
> >
> > (Record, Info, and Stats)
> > Body Sheets - 40 sheets that need renaming.
> > Chart Sheets - 4 sheets (self-explaining).
> >
> > The name-change info is located in 'Records' (AA18:AA57)
> >
> > Do I need to have it as a seperate Macro, or can I add it to an existing
> one?
>
> Either, I can't answer which is best without knowing your application.
>
>
> > Would it make it easier if I put the name-change info in the sheet I want
> > changed, and then hide the cells? And then have the macro pull the info
> > there? ( the formula would obviously have to change)
>
> Nope. Leave all the data on Records in AA18::AA57.
> You kept quiet about the sheets didn't you? I have changed the code to seek
> sheet 'Records' and only rename worksheets, not charts
>
> Sub namesheets()
> Dim arr As Variant
> arr = Worksheets("Records").Range("AA18:AA57").Value
> For i = LBound(arr) To UBound(arr)
> WorkSheets(i + 3).Name = Format(arr(i,1),"dd-mmm-yyyy")
> Next i
> End Sub
>
>
>