How to make tab name match cell on another sheet?

[Guest]

I have a set of tabs in a workbook template. I would like to have some of the
exsisting tabs automatically rename according to specific cells on another
sheet.

example:
cells 1-10 in column A of the 1st tab have text: "Week01" - "Week10"

name of 2nd tab = cell A1 in 1st tab ("Week01").
name of 3rd tab = cell A2 in 1st tab ("Week02").
name of 4th tab = cell A3 in 1st tab ("Week03").
......and so on.

 

[Dave Peterson]

You don't even need that extra worksheet.

This assumes that you either deleted it or moved it to the far right. (I used
worksheets 1 to 10--starting from the far left and going right.)

Option Explicit
sub testme()
dim wCtr as long
for wctr = 1 to 10
worksheets(wctr).name = "Week" & format(wctr,"00")
next wctr
end sub

This doesn't do any checking--if you don't have 10 sheets, it'll blow up. If
there's an existing worksheet already named Week09, it'll blow up.

 

[Guest]

Where did you place this code. I right-clicked the first tab, selected 'view
code', and put it in there. Did I do this correctly? It's not working for me.

 

[Mike Fogleman]

The code would go in a standard code Module. What you did was put it in the
Worksheet module of the sheet you right clicked on. That is one way to get
to the VBEditor (ALT+F11 is another), but once there you need to insert a
standard code Module into your VBAProject. On the left is a directory tree
listing open workbooks and any Add-Ins you have running (.xla). Right click
on your workbook project and Insert/Module. you can now Cut and Paste the
code from the Worksheet module to the standard Module. It should run OK from
there.

Mike F

 

[Dave Peterson]

You may want to read David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/getstarted.htm