VBA & Macro Problems

How to Save Excel Invoices with Sequential Numbers and Customer Names via VBA

Camila MilosovichCamila Milosovich Oct 29, 2026 869 views

Question details

The user wants to automate saving an Excel invoice template using VBA so that the saved file includes a sequential number, customer name, and date in its filename, right before clearing the template for the next use.

How to Save Excel Invoices with Sequential Numbers and Customer Names via VBA
Product
Excel
Device & OS
not provided
Scenario
Automating invoice generation and saving using a VBA macro to streamline repetitive billing workflows.
Observed behavior
The user encounters errors when trying to save the previous invoice without macros, often due to missing path separators in the file path or using incorrect FileFormat parameters for .xlsx and .xlsm extensions.
Before you start

Ensure your Developer tab is enabled in Excel and that you have backed up your original invoice template before running or testing new VBA macros.

Solution 1Recommended

Construct the File Path and Save as a Standard Workbook

Correctly build the file path string in your VBA code, ensuring proper backslashes, and use the xlOpenXMLWorkbook format to save the file without macros.

When generating filenames dynamically in VBA, a common mistake is omitting the final backslash in the folder path, which causes the file to save in the wrong directory or throw an error. Additionally, if you want the final invoice to be a standard Excel file without the macro code, you must specify the correct format.

1
Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor and locate your invoice saving macro.

2
Fix the Folder Path

Check your folder path string and ensure it ends with a backslash. For example: FolderPath = "C:\Users\YourName\Documents\Invoices\"

3
Construct the Filename

Concatenate the path, invoice number, customer name, and date. Example: NewFN = FolderPath & Range("B2").Value & "_" & Range("C2").Value & ".xlsx"

4
Save the Workbook

Use the SaveAs method with the correct file format parameter to strip the macros from the saved copy: ActiveWorkbook.SaveAs Filename:=NewFN, FileFormat:=xlOpenXMLWorkbook

Construct the File Path and Save as a Standard Workbook
File Format Verification: Using FileFormat:=xlOpenXMLWorkbook is crucial when saving as .xlsx to prevent Excel from throwing a compatibility error.
Advanced Automation

Automate Your Invoices with WPS Spreadsheet

WPS Spreadsheet offers powerful built-in tools and advanced developer features to automate your daily tasks. You can seamlessly run and edit VBA macros to generate sequential invoices, just like you would in Microsoft Excel.

  1. 1. Open Your Invoice Template: Launch WPS Spreadsheet and open your existing invoice template file.
  2. 2. Access the Developer Tools: Go to the 'Developer' tab and click on 'Visual Basic' to access the macro editor.
  3. 3. Insert Your VBA Code: Paste or write your VBA code to define the file path, concatenate cell values for the filename, and execute the saving logic.
  4. 4. Run the Macro: Execute the macro to automatically save your invoice with the custom sequential filename and clear the template.
Fully compatible with Microsoft Excel (.xlsx, .xlsm) formatsSupports advanced VBA macros for automation workflowsLightweight application with a user-friendly interfaceIncludes hundreds of free built-in invoice templates
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when saving my invoice macro as a .xlsx file?

If you are saving a macro-enabled workbook as a standard .xlsx file, Excel requires the FileFormat:=xlOpenXMLWorkbook parameter in your VBA code to confirm you intend to strip the VBA code from the new file.

How do I automatically clear the invoice template after saving?

At the end of your saving macro, you can add code like Range("A10:F20").ClearContents to target and empty the specific cells you want to reset for the next invoice.

How do I generate a sequential invoice number using VBA?

You can automatically increase the value of your invoice number cell by adding a simple mathematical operation to your macro, such as Range("B2").Value = Range("B2").Value + 1, placed right before or after the code that saves the current invoice.