Generate Work Item IDs from Excel Sheet Names with VBA in WPS Office
Question details
The user needs a VBA function to generate sequential work item IDs from Excel worksheet names that share the same six-digit prefix.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains worksheet names such as CH-2306011, and the user wants to derive a work packet ID and a two-digit sequence for sheets with the same six-digit prefix.
- Observed behavior
- Formula-only solutions based on GET.WORKBOOK may not update reliably when sheets are added or renamed.
Save the workbook as a macro-enabled workbook (.xlsm) and make sure the Developer tab is available in WPS Spreadsheets before you open the VBA editor.
Create a VBA User-Defined Function to Generate Sequential Work Item IDs from Sheet Names
Use this method when you need sequential numbering that updates reliably after sheets are added or renamed, instead of a formula-only GET.WORKBOOK approach.
This VBA function receives the current sheet name, extracts the six-digit numeric prefix, counts worksheets that share that prefix, and returns the result as a two-digit sequence such as 01 or 02.
Click the Developer tab and select Visual Basic, or press Alt+F11, to open the Visual Basic for Applications editor.
In the Project Explorer, right-click the workbook project, choose Insert > Module, and create a new standard module.
Paste a VBA function that loops through ThisWorkbook.Sheets, compares the six-digit prefix from the current sheet name, and returns Format(counter, "00").
Return to WPS Spreadsheets, click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and confirm the format.
Use the Custom Function in the Worksheet and Refresh After Sheet Changes
Apply this after adding the VBA function; it places the generated ID in a cell and updates the result after recalculation.
Generate Sequential Work Item IDs from Worksheet Names with WPS Spreadsheets VBA
Open your workbook in WPS Spreadsheets, add the VBA user-defined function to a standard module, save as a macro-enabled .xlsm file, and use the function in a worksheet cell to produce a two-digit sequence such as 01 or 02 for sheets sharing the same prefix.
- 1. Download and open WPS Office: Install WPS Office, launch WPS Spreadsheets, and open the workbook that contains the CH-2306011 worksheet names.
- 2. Enable the Developer tab: Click Menu > Options > Customize Ribbon, enable the Developer tab, and return to the spreadsheet.
- 3. Open the VBA editor and insert a module: Click Developer > Visual Basic, insert a standard module from the Project Explorer, and paste the GetWorkItemID function.
- 4. Save as a macro-enabled workbook: Click File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) so the VBA code remains available.
- 5. Enter the function and recalculate: In a worksheet cell, enter the function with the sheet name, then press F9 to recalculate after adding or renaming sheets.
Frequently Asked Questions
Why does a GET.WORKBOOK formula not update reliably when I add or rename sheets?
GET.WORKBOOK is a legacy macro-sheet function and sheet additions or renames do not always trigger a recalculation. A VBA user-defined function loops through the current workbook and returns an updated sequence when the workbook recalculates.
How do I open the VBA editor in WPS Spreadsheets?
Enable the Developer tab first. Then click Developer > Visual Basic or press Alt+F11 to open the VBA editor, and insert a standard module from the Project Explorer.
What file format should I use for a VBA function?
Save the workbook as an Excel Macro-Enabled Workbook with the .xlsm extension. If you save it as a normal .xlsx file, the VBA function may be removed or blocked.
How do I force the work item IDs to update after renaming a sheet?
Update the cell that passes the sheet name to the function, then press F9 or click Formulas > Calculate Now to force recalculation.
Can this method generate IDs for sheets that do not share the same six-digit prefix?
The example function counts only sheets whose six-digit numeric prefix matches the current sheet. You can adapt the prefix condition in the VBA code if your workbook uses a different naming rule.
