How to make tab name match cell on another sheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
 
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
 
Back
Top