How to Create and Number Duplicate Invoice Sheets with VBA in WPS Spreadsheets
Question details
The user needs a VBA-driven button in WPS Spreadsheets that copies an existing invoice worksheet, creates a new sheet, and automatically assigns the next sequential invoice number to the sheet name and a cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a workbook with an invoice template and wants to generate numbered duplicate invoice sheets by clicking a button instead of copying and editing sheets manually.
- Observed behavior
- After the button is clicked, the macro should copy the Invoice template sheet, name the new sheet with the next sequential number, and place that number in the invoice number cell.
Make sure your workbook already has an invoice template sheet named Invoice and that the Developer tab is available. Also confirm that macros are allowed for this workbook so the VBA code can run.
Add a Command Button and VBA code that copies the invoice sheet and assigns the next invoice number
Use this method when you want a reusable button on an Invoice template that creates a duplicate sheet named Invoice_N and writes the same sequential number into a cell.
Open the workbook in WPS Spreadsheets. Right-click any ribbon tab and choose Customize Ribbon, then enable Developer and click OK. When the workbook opens, if a security warning appears, click Enable Content or set Developer > Macro Security to allow macros for this workbook.
Click the Developer tab, click Insert, and under ActiveX Controls choose Command Button. Drag on the invoice template sheet to draw the button.
Right-click the Command Button and choose View Code. In the VBA editor, replace the empty CommandButton1_Click event with the following code, then add the GetLastInvoiceNumber function below it: Private Sub CommandButton1_Click() Dim newSheet As Worksheet Dim lastInvoiceNumber As Long lastInvoiceNumber = GetLastInvoiceNumber() ThisWorkbook.Sheets("Invoice").Copy After:=Sheets(Sheets.Count) Set newSheet = ActiveSheet newSheet.Name = "Invoice_" & lastInvoiceNumber + 1 newSheet.Range("B2").Value = lastInvoiceNumber + 1 End Sub Function GetLastInvoiceNumber() As Long Dim sheet As Worksheet Dim lastInvoiceNumber As Long lastInvoiceNumber = 0 For Each sheet In ThisWorkbook.Sheets If Left(sheet.Name, 8) = "Invoice_" Then lastInvoiceNumber = Application.Max(lastInvoiceNumber, Val(Mid(sheet.Name, 9))) End If Next sheet GetLastInvoiceNumber = lastInvoiceNumber End Function
Return to the invoice sheet and click the Command Button. WPS Spreadsheets copies the sheet named Invoice to the end of the workbook, names it Invoice_N, and writes the next number into cell B2. If your template sheet or invoice-number cell has a different name or location, update the sheet name in Sheets("Invoice") and the range in newSheet.Range("B2").
Automate Duplicate Invoice Numbering with VBA in WPS Spreadsheets
Use WPS Spreadsheets to add a VBA button that copies the invoice template, detects the highest existing invoice number, and creates the next numbered sheet automatically. The same workbook remains compatible with Microsoft Excel macro-enabled formats.
- 1. Install WPS Office and open the invoice workbook: Download WPS Office from the official site, install it, and open your existing Excel or WPS invoice workbook in WPS Spreadsheets. If prompted, enable macros for the workbook.
- 2. Enable the Developer tab: Right-click any ribbon tab and choose Customize Ribbon, then enable Developer and click OK. The Developer tab appears in the WPS Spreadsheets ribbon.
- 3. Insert and code the button: On the Developer tab, click Insert > Command Button (ActiveX Control). Draw the button on the Invoice sheet, right-click it, choose View Code, and paste the VBA macro that copies the sheet and writes the next invoice number.
- 4. Test and save the macro-enabled workbook: Click the button to create the next numbered invoice sheet, then save the workbook. If WPS prompts you to choose a format, select Excel Macro-Enabled Workbook (.xlsm) or WPS macro-enabled format.
Frequently Asked Questions
Can I use this VBA macro in WPS Spreadsheets if my original file was created in Excel?
Yes. WPS Spreadsheets supports VBA macros and Excel workbook formats, including macro-enabled .xlsm files. Open the workbook in WPS Spreadsheets, enable macros, and adjust the sheet name Invoice and cell B2 to match your file.
Why does the macro copy the sheet but still show the same invoice number?
The invoice number is written to cell B2 in the code. If your invoice number is stored in another cell, change newSheet.Range("B2") to the correct range, such as newSheet.Range("D4").
How does the macro calculate the next invoice number?
The GetLastInvoiceNumber function loops through all worksheets, finds sheet names that begin with Invoice_, extracts the numeric part, and returns the highest value. The new sheet uses that value plus 1.
What should I do if the Developer tab is not visible in WPS Spreadsheets?
Right-click any ribbon tab and choose Customize Ribbon, enable Developer, and click OK. If you see a security warning when opening the workbook, click Enable Content before running the macro.
Will the numbering still work if I delete or rename an invoice sheet?
The macro numbers from the highest existing Invoice_ sheet name. If you delete a later numbered sheet, that number can be reused; if you rename sheets so they no longer match Invoice_, they will be ignored. Keep sheet names consistent or store the last number separately if you need a non-repeating sequence.
