Skip to content

Excel VBA Macro Save .xlsm File to Specific Location as .xlsx | File Name Based on Cell Values

     

    In this video I demonstrate how to write VBA code to save a macro-enabled .xlsm file as a normal Excel workbook (.xlsx file). In our scenario we have an invoice template that we use to generate our invoices. A command button, when pressed, automatically saves a copy of the invoice template to a specified path, whose filename is based on the invoice number and customer name. The invoice template is then updated with the next invoice number.

    Click here to download the featured file.

    Click here to download the file without macro.

    Click here to view file format codes.

    Here’s the featured VBA Code

    Private Sub CommandButton1_Click()

Dim path As String
Dim invno As Long
Dim fname As String

path = "C:\Users\chest\Blue Pecan\Invoices\"
invno = Range("D3")
fname = invno & " - " & Range("D5")

Application.DisplayAlerts = False

Sheet1.Copy
ActiveSheet.Shapes("CommandButton1").Delete
    
    With ActiveWorkbook
        .SaveAs Filename:=path & fname, FileFormat:=51
        .Close
    End With

MsgBox "Your next invoice number is " & invno + 1

Range("D3") = invno + 1

ThisWorkbook.Save

Application.DisplayAlerts = True

End Sub

    The code will do the following:

    1) Create a copy of a worksheet that is currently in a macro-enabled workbook and place it in a new workbook.

    2) Save the new workbook to a specified folder with a file name based on a concatenation of two cell values in that sheet. The file name is based on the invoice number and customer name. The file is saved as a .xlsx file.

    3) Close the new workbook, once saved.

    4) Update the invoice number on the invoice template.

    Video Table of Contents

    00:00 – Introduction

    01:41 – Saving a workbook as a macro-enabled workbook

    02:22 – Displaying the Developer tab

    02:43 – Adding a command button to the worksheet

    03:33 – The steps the macro needs to perform

    04:44 – Creating the macro’s variables

    06:27 – The VBA code to create a copy of the invoice

    08:35 – The VBA code to delete the command button from the worksheet

    09:29 – The VBA code to save the new invoice with the path and filename specified

    11:33 – The VBA code to turn display alerts off

    13:10 – The VBA code to create a message box displaying the next invoice numbers

    13:37 – The VBA code to increment the invoice number on the template

    13:53 – The VBA code to save changes to the invoice template