Create and Rename Multiple Excel Worksheets from a Column in WPS Office
Question details
The user needs to automate copying a template worksheet multiple times and rename each copy using values stored in a column on another worksheet with Excel VBA.
- Product
- WPS Spreadsheets (WPS Office)
- Device & OS
- not provided
- Scenario
- A workbook has a template sheet and a separate list of worksheet names in a column. The user wants each valid name to become a new worksheet copied from the template.
- Observed behavior
- Manual copying and renaming multiple worksheets is time-consuming and can introduce inconsistent names or duplicate-name errors.
Make sure your workbook contains a template sheet and a list worksheet with the desired sheet names in a single column. Confirm that macro execution is enabled in WPS Spreadsheets so the VBA code can run.
Use a VBA Loop to Copy a Template Sheet and Rename It from a Column List
Use this solution when you need to create many worksheets from a template and apply names automatically from a column, with built-in checks for duplicates and invalid names.
This macro copies a template worksheet for every non-empty cell in your naming list and renames the copy. It skips blank cells, duplicate sheet names, and names that are not valid Excel worksheet names, so a single bad value does not stop the batch. Sub CreateSheetsFromColumn() Dim templateName As String Dim namesSheetName As String Dim cell As Range templateName = "Template" namesSheetName = "Sheet1" For Each cell In ThisWorkbook.Worksheets(namesSheetName).Range("A2:A100") If cell.Value = "" Then GoTo NextCell If Not SheetExists(cell.Value) And IsValidSheetName(cell.Value) Then Worksheets(templateName).Copy After:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value End If NextCell: Next cell End Sub Function SheetExists(sheetName As String) As Boolean Dim ws As Worksheet SheetExists = False For Each ws In ThisWorkbook.Worksheets If ws.Name = sheetName Then SheetExists = True Exit Function End If Next ws End Function Function IsValidSheetName(sheetName As String) As Boolean Dim invalidChars As String invalidChars = "\/:*?[]" If Len(sheetName) = 0 Or Len(sheetName) > 31 Then IsValidSheetName = False Exit Function End If Dim i As Integer For i = 1 To Len(invalidChars) If InStr(sheetName, Mid(invalidChars, i, 1)) > 0 Then IsValidSheetName = False Exit Function End If Next i IsValidSheetName = True End Function
Open your workbook in WPS Spreadsheets and press Alt+F11 to launch the VBA editor.
In the VBA editor, click Insert > Module to create a new standard module for the macro.
Paste the provided VBA code into the module. Change templateName = "Template", namesSheetName = "Sheet1", and Range("A2:A100") to match your template sheet, list worksheet, and name range.
Place the cursor inside CreateSheetsFromColumn and press F5, or click Run > Run Sub/UserForm. WPS copies the template after the existing sheets and renames each copy from the column list.
Return to WPS Spreadsheets, verify the generated sheet names, and save the workbook as a macro-enabled file such as .xlsm to keep the VBA code for future runs.
Automate Worksheet Creation from a Column with WPS Spreadsheets VBA
WPS Spreadsheets can run the VBA loop natively to copy a template sheet and rename each copy from a column list. Use your existing Excel macro workbook, keep formulas and formatting, and avoid manual copy-and-rename work.
- 1. Open your workbook in WPS Spreadsheets: Open the workbook that contains the template sheet and the column list of worksheet names.
- 2. Open the VBA editor: Press Alt+F11 in WPS Spreadsheets to open the VBA development window.
- 3. Insert a module and paste the code: Click Insert > Module, paste the CreateSheetsFromColumn macro, and adjust the template name, list sheet, and range.
- 4. Run the macro: Click inside the macro and press F5 or use Run > Run Sub/UserForm to generate the worksheets.
- 5. Save as a macro-enabled workbook: In WPS Spreadsheets, click Menu > Save As and choose Excel Macro-Enabled Workbook (.xlsm) to preserve the macro.
Frequently Asked Questions
Why does the macro fail with "Cannot rename a sheet to the same name as another sheet"?
A duplicate sheet name is usually present in the list, or the new name is the same as the template sheet name. Remove duplicates and use the SheetExists function to skip duplicate values before renaming.
How do I change the macro from Sheet1 column A to my own list?
In the VBA code, change namesSheetName = "Sheet1" to the sheet that has your names and update Range("A2:A100") to the range containing the list, such as Range("B2:B50").
Does this VBA method work in WPS Office when the file is .xlsm?
Yes. WPS Spreadsheets can run VBA macros and can save macro-enabled Microsoft Excel .xlsm files. Keep the file as .xlsm or .xls to preserve the macro.
Which characters must I avoid in worksheet names?
A worksheet name cannot be blank, cannot exceed 31 characters, and cannot contain the characters \ / : * ? [ ]. The provided IsValidSheetName function checks these conditions.
Can I create sheets from a column without VBA?
WPS Spreadsheets does not have a built-in command to create multiple sheets directly from cell values. You can copy and rename sheets manually, but VBA is the fastest way to automate the task.
