• Creator
  • #2268396

    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?

All Answers

  • Author
    • #2536719


      by laura.laughery.gud4 ·

      In reply to Inserting Worksheet Tabs in Excel 2003


    • #2536701

      The easiest way

      by tig2 ·

      In reply to Inserting Worksheet Tabs in Excel 2003

      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!

      • #2536593

        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?

        • #2536569

          I am assuming that you are referring to the sheet tab

          by tig2 ·

          In reply to Further clarification needed

          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!

    • #2536555

      Here’s a partial solution

      by locolobo ·

      In reply to Inserting Worksheet Tabs in Excel 2003

      I found this webpage,

      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.

      • #2536551

        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.

        • #2537475


          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.

Viewing 2 reply threads