How to make tab name match cell on another sheet?



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

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.


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

Mike F


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question