"copy worksheet from previous month and rename to current month" modified?

[jerrydiaz]

Hi-

There already exists an 8-year old thread using the following code to copy worksheet and rename to current month at http://www.excelforum.com/excel-prog...ent-month.html using the following code:

Please Login or Register  to view this content.

I need this exact function, with the one modification that I don't want the year included. When I remove the "yyyy" from the "mmmm yyyy" in the DateAdd function, it fails unless the existing sheet name already contains a year.

Thanks

[bmxfreedom]

Maybe I'm misunderstanding this, but to get the date out of the previous sheet name, it would have to contain a year. You could change the function completely and revolve around the month in a 1-12 cycle, but that would fail once you had 12 sheets... Why do you want the year excluded?

[jerrydiaz]

Because the company standard is to have each of the 12 tabs contain just the month name.

We have a new workbook every year with 12 months.

Is there a way to accomplish this?

[jerrydiaz]

Anybody? Please?

[bmxfreedom]

Yes, you could have a hidden sheet with a list of month names in the appropriate order, then in the macro, add the name of the last sheet to a cell on the same sheet which is reference using a formula like this: say the last sheet name is put in cell B1 and the list of months is in cells A1:A12, the formula would be:

Please Login or Register  to view this content.

, which would return the month following the last sheet. Keep in mind that the formula would return a blank value once the last sheet hit December, unless you add another January to the bottom of the list of months. Based on what you've said about creating a new book for each calendar year, I assume you wouldn't need this option though.
Anyway, the value returned by this formula could be referenced by your macro to name the next sheet. I'll try to modify the code you have to do this for you based on a hidden sheet called "dates".

[bmxfreedom]

Here, try this code:

Please Login or Register  to view this content.

It works in a test book I have designed like I suggested with months contained in a hidden sheet titled "dates". The code does the work of the formula so that you don't have to worry about it; all you need is a list of months in A1:A12. I hope this helps!

[jerrydiaz]

Thank you SO much!!