Software

Ask something
Question Locked

Inserting Worksheet Tabs in Excel 2003

0 helpful
7 replies

by laura.laughery.gud4

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

7 replies

by tig2

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!

by laura.laughery.gud4

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?

by tig2

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!

by tig2

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.

by locolobo

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.

This discussion is closed to new replies.