Скачать с ютуб Excel VBA Macro Save .xlsm File to Specific Location as .xlsx | File Name Based on Cell Values в хорошем качестве

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

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