Question

Locked

Inserting Worksheet Tabs in Excel 2003

By laura.laughery.gud4 ·
I have a user who needs to insert additional worksheets in a new workbook each month. But, they want to rename Sheet 1 to the first day of the month (i.e. 3-1-07), and then easily update the rest of the worksheets to be the rest of the days of the month (i.e. Sheet 2 becomes 3-2-07). Is there a relatively easy method to accomplish this?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

The easiest way

by Tig2 In reply to Inserting Worksheet Tabs ...

Would be to build a template and set the sheets in the workbook to numerics to identify the days of the month. That way you only have to rename the first sheet.

To my knowledge, you can't set a macro to rename tabs.

To save a template, create your frame and choose File-> Save As and select template from the dropdown box. Save to the default location and it will be available on the General tab.

Good luck!

Collapse -

Further clarification needed

by laura.laughery.gud4 In reply to The easiest way

Thank you for your assistance on this! I understand how to create a template. I do not know how to change the properties of the sheet tabs to be numerics. Can you clarify that?

Collapse -

I am assuming that you are referring to the sheet tab

by Tig2 In reply to Further clarification nee ...

Located at the bottom of the workbook "page". That field is tolerant of numbers.

Given that you can't get a macro to address the tabs, I envision a template file that has 31 pages, possibly 32. The first page is your month/year (e.g. May 2007) while the following 31 pages serve as a place to put the data. On the month page, any formulae required to pull information from the subsequent tabs is set.

Using this template, you are only required to rename one tab- the one that reflects the month/year and bearing the roll-up formulae and possibly delete numeric tabs that you do not need- the 31st day of a month with 30 days in it.

If you are more comfortable working with macros, they can also be saved to the template.

Good luck!

Collapse -

Here's a partial solution

by LocoLobo In reply to Inserting Worksheet Tabs ...

<p>I found this webpage,</p><br>

<a href="http://exceltips.vitalnews.com/Pages/T0225_Dynamic_Worksheet_Tab_Names.html">http://exceltips.vitalnews.com/Pages/T0225_Dynamic_Worksheet_Tab_Names.html</a><br><br>

<p>The code shows how to change the name for an active worksheet. It worked on my machine when I tested it. I'm not sure how to change the active worksheet, etc. Maybe it will help.</p>

Collapse -

If you use this code

by Tig2 In reply to Here's a partial solution

You will also need to insure that the data written to cell A1 (or whatever cell you choose) is the date. You still have a partially manual process.

I have tried a few different things to associate a sub-routine to the tab space and so far, the template is the most efficient in terms of clicks to accomplish the task.

Collapse -

Yah

by LocoLobo In reply to If you use this code

It looks to me like the template is the easiest to set up and use too.

The code gave me fits trying to implement it. As outlined in the tip it would do just what the tip said. Trying to get it to cycle through the other worksheets didn't work for me.

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums