How to Copy and Rename Worksheets from a List Using VBA
Question details
The user needs a VBA macro to automatically duplicate a specific worksheet named 'Template' multiple times, renaming each new copy based on a predefined list of names located in the 'Table of Contents' worksheet.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Automating the creation of multiple identically formatted worksheets (such as for different categories, regions, or employees) by pulling names sequentially from a central list.
- Observed behavior
- The goal is to eliminate the manual process of copying and renaming sheets by executing a script that loops through the provided range and applies the names directly.
Before running the macro, ensure that your list of names does not contain any invalid characters (\, /, *, ?, :, [, or ]) and that no single name exceeds the maximum length of 31 characters, as this will cause the macro to fail.
Use a VBA Loop to Copy and Rename Worksheets Automatically
You can implement a VBA script that loops through your target column, creates a copy of the template sheet at the end of the workbook, and assigns it the corresponding name from the list.
This macro works by identifying the last used row in your target list to dynamically adjust how many copies are made. It temporarily disables screen updating during execution to speed up the process.
Press the ALT + F11 keys on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar, and then select 'Module' to create a blank workspace for your script.
Copy and paste the following code into the module window: Sub CopyTemplate() Dim wTemplate As Worksheet Dim wTOC As Worksheet Dim r As Long Dim m As Long Application.ScreenUpdating = False Set wTemplate = Worksheets("Template") Set wTOC = Worksheets("Table of Contents") m = wTOC.Range("A1").End(xlDown).Row For r = 2 To m wTemplate.Copy After:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = wTOC.Range("A" & r).Value Next r Application.ScreenUpdating = True End Sub
Ensure your workbook actually contains a sheet named exactly 'Template' and another named 'Table of Contents'. If your sheets have different names, update the code accordingly.
Close the VBA editor. Back in your spreadsheet, press ALT + F8 to open the Macro dialog, select 'CopyTemplate', and click 'Run'.
Automate Tasks with Macros in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to run your Excel scripts smoothly to automate repetitive tasks like copying and renaming worksheets.
- 1. Enable Macros in WPS: Open WPS Spreadsheet, navigate to the 'Developer' tab, and click 'Macros' to manage or run your automation scripts.
- 2. Access the VBA Editor: Click the 'Visual Basic' icon in the Developer tab or press ALT + F11 to launch the fully integrated VBA editor.
- 3. Run Your Script: Paste your custom scripts into the module and press F5 to execute them, effortlessly creating and renaming your worksheets.
Frequently Asked Questions
What happens if a worksheet name in the list already exists in the workbook?
If a name from your list matches an already existing worksheet, the VBA macro will encounter a 'Name already taken' runtime error and pause. You should ensure all names in your list are unique and not currently used in the workbook.
Why do I get a 'Subscript out of range' error when running this macro?
This error typically occurs if the macro cannot find the worksheets specified in the code. Double-check that your workbook has sheets named exactly 'Template' and 'Table of Contents' with no extra spaces.
Can I modify the macro to pull names from a different column?
Yes. In the provided script, change the references from 'A' to your desired column letter (for example, change Range("A1") to Range("B1") and Range("A" & r) to Range("B" & r)).
