How to make tab name match cell on another sheet?


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.
 
Ad

Advertisements

D

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.
 
G

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.
 
M

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
 
Ad

Advertisements


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

Top