How to Create Worksheets from a Selected Range of Names via VBA
Question details
The user needs a VBA script that prompts them to select a variable range of cells and automatically generates new worksheets named after the text in those cells, while reporting the total number of created sheets.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating the batch creation of worksheets based on a dynamically sized list of names stored in a spreadsheet.
- Observed behavior
- The desired macro must successfully prompt for user selection, trim values, remove invalid worksheet-name characters, enforce the 31-character limit, skip blanks or duplicate sheets, and summarize the creation output.
Ensure that the Developer tab is enabled in your spreadsheet application and that macros are allowed to run in your Trust Center or Macro Security settings.
Implement a VBA Macro to Generate Worksheets from Cell Names
Use this comprehensive VBA script to display an InputBox for dynamic range selection, automatically sanitize cell text to meet worksheet naming rules, and generate the required sheets seamlessly.
This solution utilizes 'Application.InputBox' to allow dynamic range selection. It also includes helper functions to filter out invalid characters (like colons or slashes), truncate names exceeding 31 characters, and check for existing worksheets to avoid duplicate errors.
Launch your spreadsheet application, open your workbook, and press 'Alt + F11' to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, click on 'Insert' in the top menu bar, and select 'Module' to create a blank script window.
Copy and paste the following VBA code into the module window: ```vba Option Explicit Sub Test() Dim Source As Range, This As Range Dim Ws As Worksheet Dim WsName As String Dim i As Integer On Error Resume Next Set Source = Application.InputBox("Where are the worksheet names?", Type:=8) On Error GoTo 0 If Source Is Nothing Then Exit Sub Set Source = Intersect(Source, Source.Parent.UsedRange) If Source Is Nothing Then Exit Sub For Each This In Source WsName = Trim(ValidSheetName(This.Value)) If WsName = "" Then GoTo Skip If SheetExists(WsName) Then GoTo Skip Set Ws = Worksheets.Add(After:=Sheets(Sheets.Count)) Ws.Name = WsName i = i + 1 Skip: Next This MsgBox i & " sheets created" End Sub Private Function ValidSheetName(ByVal SheetName As String) As String Const InvalidChars = ":\\/?*[]" Dim i As Integer For i = 1 To Len(InvalidChars) SheetName = Replace(SheetName, Mid(InvalidChars, i, 1), "") Next i ValidSheetName = Left(SheetName, 31) End Function Private Function SheetExists(ByVal SheetNameOrIndex As Variant, Optional ByVal Wb As Workbook = Nothing) As Boolean On Error Resume Next If Wb Is Nothing Then Set Wb = ActiveWorkbook SheetExists = Not Wb.Sheets(SheetNameOrIndex) Is Nothing End Function ```
Close the VBA editor or return to your worksheet. Press 'Alt + F8', select the 'Test' macro, and click 'Run'. When the InputBox appears, drag your mouse to select the range of cells containing the names, then click 'OK'.
Automate Worksheet Creation Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers excellent compatibility with VBA macros, allowing you to run complex automation scripts like dynamic worksheet creation effortlessly.
- 1. Download and Install WPS Office: Download WPS Office for free from the official website and install it on your computer.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your list of worksheet names.
- 3. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click on 'Macro' to access the built-in VBA editor.
- 4. Run the Script: Paste your worksheet creation VBA code and execute it directly within WPS Spreadsheet to automate your workflow instantly.
Frequently Asked Questions
What characters are invalid for worksheet names?
Spreadsheet applications do not allow colons (:), backslashes (\), forward slashes (/), question marks (?), asterisks (*), or square brackets ([ ]) in worksheet names. The provided VBA script automatically searches for and removes these characters before creating the sheet.
Why is there a 31-character limit on worksheet names?
By default, both Microsoft Excel and WPS Spreadsheet restrict sheet tab names to a maximum of 31 characters. The `ValidSheetName` function in the script automatically truncates any name longer than this limit from the right side.
Will this macro overwrite my existing worksheets if a name duplicates?
No. The macro includes a dedicated `SheetExists` helper function. If a cell value matches the name of a worksheet that is already in the workbook, the script will skip that name and move on to the next cell, protecting your existing data.
How do I fix the 'Application-defined or object-defined error' in VBA?
This error usually occurs when attempting to create a worksheet with an invalid name or exceeding character limits. Using the sanitized inputs from the script's helper functions (trimming spaces, removing bad characters, capping at 31 characters) prevents this issue entirely.
