How to Copy an Excel Sheet 12 Times and Rename by Month Using VBA
Question details
The user needs to automate the creation of a 12-month workbook by copying a single template worksheet 12 times and renaming each duplicated sheet to a consecutive month (January through December) for the current year.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a monthly tracking workbook where an identical template needs to be replicated and sequentially labeled by month and year.
- Observed behavior
- Instead of manually duplicating and renaming sheets 12 times, an automated VBA macro is required to perform the copying, renaming, and hiding of the original template.
Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to allow VBA code execution, and verify that the sheet you intend to duplicate is named exactly 'Template'.
Use a VBA Macro to Duplicate and Rename Sheets Automatically
This solution uses a standard VBA loop to duplicate the 'Template' sheet 12 times, automatically generating sequential month names (e.g., January 2023) using the DateSerial function.
The provided macro will unhide the template (if hidden), loop through the numbers 1 to 12, create a new sheet for each month, and apply the month and current year to the sheet tab name. Finally, it hides the original template to keep your workbook clean.
Open your workbook, right-click the worksheet tab containing your template, select 'Rename', and type 'Template' (without quotes).
Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.
In the top menu, click 'Insert' and then select 'Module' to create a blank coding window.
Copy and paste the following VBA code into the new module window: Sub CreateMonthlySheets() Dim k As Integer Dim shName As String Sheets("Template").Visible = xlSheetVisible For k = 1 To 12 shName = Format(DateSerial(Year(Date), k, 1), "mmmm yyyy") Sheets("Template").Copy After:=Sheets(Sheets.Count) On Error Resume Next ActiveSheet.Name = shName On Error GoTo 0 Next k Sheets("Template").Visible = xlSheetHidden MsgBox "Job Done" End Sub
Press F5 or click the 'Run Sub/UserForm' button (the green play icon) on the toolbar. The code will execute, generating the 12 formatted monthly sheets.
Run Macros and Manage Workbooks Smoothly with WPS Spreadsheet
WPS Office offers full support for VBA macros, allowing you to seamlessly execute sheet duplication scripts just like in Microsoft Excel. It provides a familiar interface to streamline your data management tasks.
- 1. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the template sheet.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor', or simply press ALT + F11.
- 3. Run the Sheet Duplication Macro: Insert a module, paste the monthly sheet generation code, and click the 'Run' button to instantly create your 12 monthly tabs.
Frequently Asked Questions
Why do I get a 'Subscript out of range' error when running the macro?
This error occurs if the VBA code cannot find a sheet named exactly 'Template'. Ensure your original worksheet is named 'Template' with a capital 'T' and contains no trailing spaces.
How can I format the sheet names to only show the month?
To display only the month (e.g., 'January' instead of 'January 2023'), modify the Format function in the code. Change `"mmmm yyyy"` to `"mmmm"` before running the macro.
Will this macro overwrite existing sheets if they have the same name?
No. The code includes `On Error Resume Next`, which prevents it from crashing if a sheet with the target name already exists. However, the duplicated sheet will retain a generic name like 'Template (2)'. It is best to run this macro in a fresh workbook.
Can I change the macro to generate sheets for a specific year?
Yes. The current code uses `Year(Date)` to fetch the current calendar year. To specify a year, replace `Year(Date)` in the `DateSerial` function with your desired four-digit year, such as `2024`.
