Renaming TABS via Macro to match days of the new month

[BadKornFlake]

This has to be in this form somewhere but I must not be wording it in the search correctly. So please point me in the right direction if I am in error.

I currently have a workbook master file that I have to copy at the start of each month to track sales for that month. As part of the change I have to rename the TABS
For example this month is 12.01.11, 12.02.11, 12.05.11 (note that the 3rd and 4th are weekends and have no data so I skip them)
Now January roles around and 01.02.12, 01.03.12 and so on.

I am very new to Macros but I am also confident that a Macro could do this but I cant seem to get off the launch pad here.

If it matters I do have a totals page which does a lot of tab cell referencing ie "=sum('12.01.11:12.30.11'!H44)" That formula obviously changes month to month.

Thanks in advance for any help.

[arlu1201]

A quick question - do you want the existing tabs to be renamed? Or should these tabs be created in a fresh blank workbook?

[BadKornFlake]

I would say existing. I usually do the changes and then "Save as" a new month. I have a Master template workbook that has the format and base information I need.

Thanks,
Russ

[BadKornFlake]

I would say existing. I usually do the changes and then "Save as" a new month. I have a Master template workbook that has the format and base information I need.

Thanks,
Russ

[arlu1201]

Do you have any other sheets other than the sheets with dates? If no, should all the sheets from 1 to end be renamed with the date? What if you have only 30 and the month has 31 days? Do we make a copy of an existing tab and rename it to 31?

[BadKornFlake]

Yes there are 2 more sheets other than the dated tabs. We currently use business days only (no sales during weekends and Holidays) But, if is an easier method to do all 31 Days (ignoring the 31st when only 30 days)

[mikerickson]

You could try something like this.

First create a workbook that has two sheets named Begin and End with End right after Begin.
Then run this macro. It will create 31 worksheets between Begin and End.

Please Login or Register  to view this content.

Then delete that macro (or never run it again).

Then this will clear the data from the sheet, rename them to the month specified and hide Saturday's and Sundays.
Currently the clear data section is a blunt instrument (clears all cells), but that could be altered to leave headers etc un-cleared.

Please Login or Register  to view this content.

One use of the Begin and End sheets is that on your summary sheet, you can use the unchangin formula =SUM(Begin:End!H44), rather than the varying "=SUM('12.01.11:12.30.11'!H44)"

[BadKornFlake]

Perfect!
Thanks Gang!