Download the featured file here: https://www.bluepecantraining.com/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.

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.

Here's the code featured in the video:

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


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
------------------------