Excel VBA Macro Save .xlsm File to Specific Location as .xlsx | File Name Based on Cell Values
Вставка
- Опубліковано 2 лип 2024
- Download the featured file here: 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
------------------------ - Навчання та стиль
I wish your channel was available back when... Your step bby step explanations are brilliant! Thank You
Great one Chester! I created a macro similar to this to save a copy / paste values version of my .xlsm as .xlsx to send to clients, so they don't have control of my live workbook. Very handy and useful. Thanks for sharing. Thumbs up!!
Very easy to follow and comprehend your explanation. Thank you very much.
Chester, I can't tell you how long I've been looking for this solution. And how you don't already have a million subs I just don't know but iam sure you ll get there soon with videos like these. Thank you so much for the great explanation and video...keep it up.
Wowww. Great video Chester. Thank you for the detailed explanation!
It's easy to follow and your explanation is very clear. Subscribed~
Great content, easy step-by-step instructions.
Chester, you are the best👍👍🌹🌹
thank you Chester for the steps by steps guide. :)
Very Helpful Exercise... Thanks..
Thank you. You helped me to do my first macro. And is working :)
Excellent video, thanks a lot!
This was so helpful! Thank you!
Glad it was helpful!
Excellent Video ....😊
Thanks , it was an educational video. 🙏
Glad it was helpful!
Great !!! Thank you !!!
Great sir
This is a brilliant video @Chester Tugwell thank you for making this available for us. Can we save files to google drive folder destinations?
Another great video with detailed explanation 👌.
One question i.e. what if i want to save the newly created invoice in same folder where master invoice file is placed. Please guide.
Thanks
Great Explanation !
also, can we retrieve data from add record sheet to main invoice sheet ?
I have used your instructions. It works well. I save it as a .xltm file, because I need it use it again and again for new costing. But only problem is when I open that .xltm file it's open with saved name. Only invoice number changed. I don’t know I made a mistake. Please advise me how to save .xltm sheet in a specific location with a name and invoice number as a .xlsm file. But original .xltm file have to open as fresh file to reenter new data and save. Thank you.
Well done Chester, great vid. How would i go about inserting a date that has been formulated =IF(C5="","",(B5)) and the cell formatted to show "01/02/24" to replace an invoice number? i have attempted as per your instructions but instead of the date i am getting 45323. Thank you.
hi chester, what about the vba code for saving Word.doc and docx (some people use the older version) or even a .jpg in a specific folder. And save it as the ID # of that specific student. thanks for your help and of course pull it up from a command button-VBA and and changes on that Word doc should also be saved on the same folder with the same ID #. I am using a Userform.
Hi Chester. This is a really a great tutorial. I have a query. Rather than copy one sheet to the newly created workbook. If I have two worksheets from the original workbook to be copied over to the new one, I am wondering how to revise the VBA code to do that. Thank you.
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Copy
Just fill in the "Sheet1", "Sheet2", etc with your Sheet Name
Am able to make a copy but get Run-Time Error 1004 "The file could not be accessed" . Debug highlights the following:
.SaveAs Filename:=path & fname, FileFormat:=51
Trying to save in documents folder for testing.
Hi Chester. This is a brilliant video. Is there away to do one button to save as .xlsx and pdf at the same time.
thanks. everything works but it doesn't add the file extension. Records come as files only. Why?
Hi Chester.
I need one help this please.
First of all this is a great tutorial. This helped me in my project. However, i have a question.
I need the macro to select two specific sheets from the original workbook and copy it over to the new one.
This video only tells us about copying one sheet.
Can you please tell me how to copy over two sheets?
hi SB. I am wondering if you have found the solution? If you have, please share. Thanks
Hi Chester. How if i want add code sheet1 copy and pastevalue after auto save as a new workbook ?
possible to do this in Mac excel?
How can I run the macro to simply open the 'save as' option where I can rename and specify the file type? Same with the PDF function. I am totally illiterate when it comes to excel and getting so frustrated trying to figure out codes. can anyone help
if i run it from the code it works a treat, but cannot get the button to work ........... any ideas please
Waw
Sir,
I have a question.
When I saveAs file as macro enable using vba. Why the button doesn't work in new workbook?
Why the module not transfered there?
Thank you.
I have followed your steps and got the automated invoice I wanted, thank you so much, however I have 1 probem with it - When I save the files the code is deleting the buttons as I wanted but it is also deleting the Logo, I have tried it as a Jpg, Ico, Gif. Png and pspimage yet every time I run the code it deletes the Logo What am I doing wrong, I have been trying to nut out the problem for 2 days wth no success. The odd part is I had icons remaing when I locked the icon to the shape.
Any suggestions will be appreciated
Thanks
I like your version of Excel for dummies like me.
If you are having trouble with your logo being deleted use this code in place of the loop I demonstrate in the video:
For Each shp In ActiveSheet.Shapes
If shp.Type msoPicture Then shp.Delete
Next shp
@@ChesterTugwell OMG thank you Chester,your bloods worth bottling, that worked thank you so much
❤👍