Download the featured file here: https://www.bluepecantraining.com/exc...

In this video I demonstrate how to write VBA code to save a macroenabled .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 macroenabled 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 macroenabled 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