Excel VBA: How to Copy and Rename Worksheets Using Cell Values
Question details
Automate the process of creating and renaming new worksheets from a template based on a list of unique cell values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating multiple customized worksheets from a master template without losing formulas, tables, or formatting, using a predefined list of names in a specific range.
- Observed behavior
- Manually copying a template sheet and renaming it for dozens of entries is tedious and prone to error. A VBA macro is required to batch process this task efficiently.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in your spreadsheet program to access the VBA editor.
Use a VBA FOR Loop to Copy and Rename Worksheets
Run a standard VBA macro that loops through a designated list of cell values, copies your template sheet, and names each new sheet automatically.
This script uses a 'For Each' loop to read through a specified range of cells. For every non-empty cell it finds, it creates a duplicate of your target template sheet and immediately renames it to match the cell's value. This preserves all embedded objects, macros, and formulas.
Press 'Alt + F11' on your keyboard, or navigate to the 'Developer' tab and click on 'Visual Basic' to launch the VBA Editor.
In the top menu, click 'Insert' and then select 'Module'. This will open a blank coding window.
Input a standard loop script. For example, use 'For Each cell In Range("A2:A10")', followed by 'Sheets("Template").Copy After:=Sheets(Sheets.Count)', and 'ActiveSheet.Name = cell.Value'.
Click anywhere inside your pasted code and press 'F5', or click the green 'Run' triangle in the toolbar to execute the batch creation.
Use WPS Spreadsheet to Run VBA Macros Seamlessly
WPS Spreadsheet offers robust support for VBA macros, allowing you to seamlessly execute scripts that copy and rename worksheets. It features a familiar developer interface for automating all your spreadsheet tasks.
- 1. Open the Workbook in WPS: Launch WPS Spreadsheet and open your .xlsm file containing the data list and template sheet.
- 2. Access the Developer Tools: Click on the 'Developer' tab in the top ribbon and select 'VBA Editor', or press 'Alt + F11'.
- 3. Insert the Macro Code: Go to 'Insert' > 'Module' and paste your worksheet generation script.
- 4. Execute the Code: Press 'F5' to run the macro and watch your new worksheets generate automatically.
Frequently Asked Questions
What happens if a cell value contains duplicate names?
If your macro attempts to rename a newly copied sheet to a name that already exists in the workbook, it will trigger a runtime error. You should ensure all cell values in your list are unique, or add an error-handling routine like 'On Error Resume Next' to your VBA code.
Will copying the template sheet keep my formulas and formatting intact?
Yes. Using the 'Worksheet.Copy' method in VBA creates an exact replica of the template sheet. All cell formatting, tables, charts, embedded objects, and formulas are fully preserved in the new sheet.
How do I ignore blank cells when running the copy macro?
You can wrap your copy and rename commands inside an 'If' statement within your loop. For example, add 'If cell.Value <> "" Then' before the copy command, which forces the macro to skip any empty cells in your selected range.
