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