Copy Sheet & Create New Monthly Sheet From Present Sheet

I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?

Also rename the new sheet (February) cell B3 the same as new sheet's name (February)

So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.

Thank you in advance

1) Put this formula in B3 so that you can have the sheetname always appear on its own without needing VBA:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

2) We're always copying the current LAST sheet in the workbook, correct? Then renaming it? Also, is there any particular cells or range of cells you want "reset" or cleared of their prior values?

Yes, you're both right JBeaucaire & JimmyA, thank you very much. JBeaucaire's code is just what I want to copy Sheet's name and JimmyA's code is much better than I thought.

To answer JBeaucaire's question regarding clearing cells is yes, the cells I want to clear are B8:B12 and B30:AF31

Maybe JimmyA may be able to add codes to clear those cells.

Brilliant Jim, thank you very much for such quick reply. 'ave a good night sleep mate and yes I'll up your reputation as well to JBeaucaire

This is a automatic macro. It will copy the last sheet in the workbook then rename it based on evaluating the B3 value from the copied sheet.

Please Login or Register  to view this content.

Thank you JBeaucaire for new codes, I'll try this out as well

Cheers mate

Meet2mayank,

Be sure to read through the Forum Rules so you can use and follow them effectively. Your post breaks rule #2. You must start your own thread with your own questions and your own criteria.

When you post your thread, you can include links to this thread or code as you deem relevant.