This is a great tutorial. This trick is going to save me so many thousands of clicks! It's saving my hands my muscles and my mind from permanent damage. Thanks so much!
I learnt so much from your videos, keep using what i learnt in real worklife situation. Although i have so many unresolve things, but Thank you so much for your tutorial
Mr. Takyar I looked your youtube video "Save Excel file usign data from cells & single click" very useful but I want to go a step further, by given leave existing file as it is and creat new file name as one of cell reference
Is it possible to automate the creation of a folder and save the file in the new folder? Thank you for the video the information helped me tremendously.
Yes. Use the mkdir command. So if you have a folder called mydata in your Hard Disk Drive C, then using mkdir "C:\mydata\mywork" will create a new folder called mywork in mydata in C.
Hello, first I would like to thank you for such a great and helpful video. I have an issue with 2010 excel, code works and saves the way I would like to see it, however all my formatting erases. I created file in xlsm and added many validation lists, therefore I need to keep my file is this format. Whenever is saves it - file becomes without any formatting. Please help!!!! Thanks a lot in advance.
Hi sirji also try make video on power bi actually working as VBA developer but want to switch as power bi developer if possible please make video on power bi way your teaching is very easy and easily undestandable
Hi, is it possible to save a copy of the same worksheet in a different folder for each new day of work that i have done? So this folder will act as my backup of work for all the days. Thanks!
Thank you so much for the video. how can i write he code (based on 3 cell ranges) under save as or save without designating the path, and if the cells are blank, it will keep the default name? Thanks in advance.
Hi, This might be a stupid question, but I'm only trying to use VBA to help me sorting out my administrative tasks in less time (nagging boss). My question is, how can I save only one sheet as a PDF using data from certain cells to form the filename? Thanks a lot for your wonderful work!
Hi, i have built a worksheet that i use daily but i want to be able to click on a button and have it create a backup copy in another folder for each new workday. Is this possible? So, in this "archive", folder, i can hv a backup of my worksheet for each new day that i've created. Thank you!
Hello, is it possible to save the file as a text file? Also, is it possible to save only a specific range in that text file instead of the whole workbook? Thanks!
Hello Sir,All your videos are really awsome and very much useful for a beginner like me.I have a tried this too and works well.But I have a question.I work on an excel sheet by default and copy paste it in each folder that I work.I want to save this file according to a specific name and get it saved in the same folder were I am working in and delete the default file from that particular folder.What can I do for that.
thanks for nice explanation. I have a question pleas : in MS word 2019, if I need to add a command button inside the word FORM or template, THIS TEMPLATE HASE SOME FIELDS TO FILL IN the fields are all in type plain text content controls. I want to write a code for the command button that save the document as the content of “student name” field (plain text content control), and to save it into a specific bath directory, how can I write the code pleas ? can you drop the line code that do this when I click in this command button.? pleas your answer and thank you in advance. (PS I don’t want to print the command button in paper when I print out the document . can I make the command button unprinted?)
Thank you for the most valuable info. I have one question. I want to save into multiple files based on each cell value. Is it possible to have more than one file with different names? Thank you for all!
Hi Sir, can we do the same for saving web page with a specific name from specific cell as the file name if so please make a video on that this will be helpful to me. Thanks
hello, the clips for vba provided in youtube is very helpful, even i have acted as per the Save Excel file using data from cells & single click, Now the problem is i want to save file including the date on which file is saved...want a help
Hi, I have a issue. I am automating a webpage to download a document from it using a vba script. I can click the download option but upon clicking i get a pop below the window like, { open, save as and close}. Alt + S works fine for saving the document. Can you provide me a solution for how to go about it! I have tried using send keys but in vain.
Many thanks Dinesh, very helpful, works perfectly, one question, how can I remove all spurious tab spaces if I am outputting to MS-DOS text file from this process? I am concantenating ; but get a result like this: A04001-0 ; 1061003K instead of: A04001-0;1061003K the file type I am using is: xlTextMSDOS - any thoughts would be much appreciated
I have a question and am looking for a solution. I currently have a sheet with numerous option buttons for certain categories. Each computes a total score based on what was selected. How do I save the selected choices without them being effected when I change them in the future? Thanks!
I have a sheet on excel with option buttons that provides answers to numerous questions. After all option buttons are filled in a total score is calculated for each individual that takes the "test". How do I save each individual's answers without them being changed each time somebody else takes the test? Basically the answers selected for each option button need to be saved for each person to perform the test repeatedly. Thanks.
Or having something similar where if you select the name of the participant in a drop down list, their selected answers on the option buttons would appear.
These ideas on VBA will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/ ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ “C:\Users\takyar\Desktop\test-save.pdf”, OpenAfterPublish:=True www.exceltrainingvideos.com/how-to-save-microsoft-excel-file-as-pdf-file/
Is it possible to save a copy of an excel file using the data that is in B1 with a date time stamp and save the copy as a read only file? I have a folder on my desktop named data that I want to save it to. I want the file to be called the part number that is in B1. That will change based on the parts but than I want it to have a date formatted yyyy_mm_dd hh mm AM PM and have it as a read only copy. It is so people can go back to see how it was running at a certain time and view the parameters without being able to change them.
Check out these links: www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/ www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/ For read only you'll need to add the 'read-only' flag.
Hello, I have one question about this video, because I tried to do it like in video but document is not saved and there is no error warning. Before entering VBA formula I saved document. So, I want to use one document which is formated in advance and after some datas in cells change I want to save document with this datas, So all the time I want to have one basic document and after changing data I want to save it with different name. Please let me know does this works just with unnamed document or can I use it like I described? Thank you.
I can use some help please. I am using Excel 2016. I followed the steps from the video, but in my Excel doc the button isn't "clickable". When I click on it, it like it wants me to edit it(move it). I have enabled the macros per this article: support.office.com/en-us/article/Enable-or-disable-macros-in-Office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6
On an activex button control use the following code: Application.GetSaveAsFilename Alternatively you can create a macro with the above line of code and assign it to a form-control button. Maybe you would like to invest in the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU Or visit www.exceltrainingvideos.com to learn more for free.
Sir, Instead of defining 3 different filename variables for storing cell values using Range function. can't we just define one single variable for storing cell values using Range function or ant other function.
Hello, I figured out how to save as a text file. Use this: ".txt", FileFormat:=xlText instead of : ".xls", FileFormat:=xlNormal I would still need to know how to save only a specific range. Thanks!
Hi Sir, Thanks for your service. my doubt is , How to get a specific values from cells and save as pdf in single click . ? I want to sort list the values that has save as pdf .. please teach me how sir ?
Sub FileNameAsCellContent() Dim userPath As String Dim FileName1 As String Dim FileName2 As String
userPath = Environ("UserProfile")
'Edit the save location here with first folder location after C:\Users\[user]\ or leave to save to Desktop Const LOCATION As String = "\Desktop\"
'Select which cells to use in filename here Application.DisplayAlerts = False FileName1 = Range("F5") FileName2 = Range("B6") With ActiveSheet 'Edit filename format here or keep as default (default text - text.pdf) PDFFullName = userPath & LOCATION & FileName1 & " - " & FileName2 & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFullName, Quality:=xlQualityStandard, OpenAfterPublish:=False Application.DisplayAlerts = True End With End Sub
I have a sheet on excel with option buttons that provides answers to numerous questions. After all option buttons are filled in a total score is calculated for each individual that takes the "test". How do I save each individual's answers without them being changed each time somebody else takes the test? Basically the answers selected for each option button need to be saved for each person to perform the test repeatedly. Thanks.
Thank you I appreciate the help! Is there any way to connect those option buttons to a combo box or drop down list with the students names to keep it all in one excel file rather than saving an excel document for each student
Sir, Greeting of the Day! I am doing accounts work. mu question is ,,,, Example- when I made a profit and loss ac in excel 2007 for the mo april-15 and the next month i will change only month name and figures.I want that both months figures add and save on next sheet.
Hi, I'm trying to save an excel file with data from 4 cells. One of these cells contains a date format "year-month-date". Can this be used to make this save button work? I manage it without that 1 date cell using the other 3 without a problem. Thanks FYI: Cell 1: M Cell 2: 160101 (date cell with custom date format) Cell 3: D Cell 4: 23 Ideally this would save as M160101D23
+Capt Med Did you try it out? Shouldn't be a problem. If you have a problem, convert the date to text before using it as shown here: www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/
+Dinesh Kumar Takyar Hi D, sorry, I'm a total new comer to VBA. I guess I have it in the wrong order. Private Sub CommandButton1_Click() Dim Path As String Dim FileName1 As String Dim FileName2 As String Dim FileName3 As String FileName1 = Range("I7") Dim Path As String, mydate As String mydate = Range("J7") mydate = Format(mydate, "yymmdd") FileName2 = Range("K7") FileName3 = Range("L7") ActiveWorkbook.SaveAs FileName:=FileName1 & mydate & FileName2 & FileName3 & ".xls", FileFormat:=xlNormal End Sub
the macro works. but i got a problem after the save. i use excel 2010. the file format is .xlsx. so i change your code on the bottom line, from FileName2 & ".xls" to FileName2 & ".xlsx". after clik the save button,everything is oke, but when i want to open the file, it said the file is corrupted. any idea how to solve this?
Yes. I tried it without changing anything. It works perfect. But i got some file that i want it to be saved in .xlsx file format. The reason is, inside that xlsx file,there is some formula that can only work in xlsx format. If i use xls format, the formula won't work and it wil bw #### or #Value!# (something like that)
Hi Dinesh Kumar, I've a query! Let me store Date(Jan 2019 to Dec 2019) as a column in an excel, and i have get each row of the excel input and store in next next new word document automatically Is this possible ? For Example : In Excel: File name: Sheet 1 Date( Column) 01/ 01/2020 02/01/2020 03/01/2020 In word: File name: 01/01/2020 01/01/2020 File name: 02/01/2020 03/01/2020 File name: 03/01/2020 03/01/2020
Hi Sir please help me to save data in specific path and it will vary based on employee ID say EID is 10001 in india folder and EID 10002 in Buthan folder in single click upload the video for the same
use this pal, the setting the workbook name as something else will avoid crashes and the file format down at the bottom should be the correct one for macro enabled documents Set wb = ThisWorkbook wb.SaveAs Filename:=Path & Filename1 & "_" & Filename2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Hello, I followed your instructions on this video to Save the Excel file using data from a particular cell on the workbook by creating the Active X button. I wrote the code in VBA the same way as you and the button works but after I press the SaveFile button I get an error. The error says "compatibility checker" and "minor loss of fidelity". I believe this is because I am using Excel 2013. Should my line of code with fileformat be different than yours in order to correct this? This is what I have now: Private Sub CommandButton1_Click() Dim Path As String Dim FileName1 As String Path = "I:\1RMWQUOTEFORMS" FileName1 = Range("H1") ActiveWorkbook.SaveAs Filename:=Path & FileName1 & ".xls", FileFormat:=xlNormal End SubThank you!!
+Tommy Shinn Change this line of code: Path = "I:\1RMWQUOTEFORMS" to Path = "I:\1RMWQUOTEFORMS\" Notice how a small error can cause such a massive effect!
+Dinesh Kumar Takyar Yes!! Thank you very much, it is now working correctly! One more question, it is saving as a 97-2003 file format ".xls" I am using Excel 2013, what would I have to change in order to save it as a ".xlsx" file format so I am not running in compatibility mode? Thank you!
this is my formula so far its not saving as a pdf Private Sub CommandButton1_Click() Dim Path As String Dim Filename1 As String Path = "C:\Users\abram\OneDrive\Abrams Electrical Services Pty Ltd\Quotes and Invoices\Completed Jobs and paid" Filename1 = Range("G10") Filename2 = Range("B7") Filename3 = Range("C7") ActiveWorkbook.SaveAs Filename:=Filename1 & "-" & Filename2 & "-" & Filename3 & "pdf", FileFormat:=xlNormal End Sub
Dinesh Kumar Takyar thank you so very much for your reply. The “form” I used this with is on a shared hard drive. Works perfectly on my computer but when I open on others it doesn’t. Maybe I need to change something ?
Sir could you please help me getting my request as I am trying to figure it longtime. can you please reply with your email so I can send my Excel file. I have a really good code done by me from different videos which actually save as PDF and send the list of sales for each Individual speratly. but problem is I couldn't figure out to send as Excel file. currently it's sending as a PDF. using the formula of exportasfixedformat xltypepdf could you help me a code to get a sheet5.activesheet to be send as a Excel. it's auto attached to mail even.
Excellent question! You can try this code attached to a command button. Let me know if the code solved the problem: Private Sub CommandButton1_Click() Dim Path As String Dim mydate As String Dim FileName1 As String Dim FileName2 As String Path = "C:\inventory\" FileName1 = Range("A1") mydate = Range("B1").Value mydate = Format(mydate, "mm_dd_yyyy") FileName2 = mydate ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & ".xls", FileFormat:=xlNormal End Sub
How to save excel files having the same name but numbered 1,2,3,4, etc. in one folder (with the same name as the files )automatically without having to choose a folder?
At first, the video is great and the formula works! Thank you very much!! =) Second, I have a question: Could I save just 1 sheet (not entire workbook). In my case my workbook have 3 worksheets. 1st sheet is for clients with final prices, 2nd sheet is for the company with calculated costs 3rd sheet have prices for final prices and for calculating costs. I would like to make a 2nd BUTTON to save as: costumer only or company only... hope you got me and you can help me p.s. i tried to kick in the formula AciveSheet but code say error =)
I have updated this to work with Excel 2019 and to create a PDF: Sub FileNameAsCellContent() Dim userPath As String Dim FileName1 As String Dim FileName2 As String
userPath = Environ("UserProfile")
'Edit the save location here with first folder location after C:\Users\[user]\ or leave to save to Desktop Const LOCATION As String = "\Desktop\"
'Select which cells to use in filename here Application.DisplayAlerts = False FileName1 = Range("F5") FileName2 = Range("B6") With ActiveSheet 'Edit filename format here or keep as default (default text - text.pdf) PDFFullName = userPath & LOCATION & FileName1 & " - " & FileName2 & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFullName, Quality:=xlQualityStandard, OpenAfterPublish:=False Application.DisplayAlerts = True End With End Sub
even though i copy your code EXACTLY what you do it doesnt work. enough for me to give up. i have followed at least 6 videos to try to copy EXACTLY what you do and not one has worked - Unbelievable. you videos are clear and i can follow along. oh well not for me i guess.
Copy and paste doesn't work. After pasting the code you should check it line by line and you'll detect a few 'funny' errors or characters. Correct them and you'll smile again!
this is very useful if it worked can someone help me out Dim Path As String Dim filename1 As String Dim filename2 As String Path = "C:\Users\dominic\OneDrive\Documents\Abrams Electrical Services\Abrams Electrical Services Pty Ltd\Quotes and Invoices\Unpaid Invoices" filename1 = Range("G10") filename2 = Range("B10") ActiveWorkbook.SaveAs Filename: = Path & fileName1 & "-" & FileName2 & ".xls", FileFormat:xlNormal
Dominic Abrams also what is a VB project? it wont let me save.. says " VB projects and XLM sheets cannot be saved in a macro-free workbook." What does this mean?
Hi, i have built a worksheet that i use daily but i want to be able to click on a button and have it create a backup copy in another folder for each new workday. Is this possible? So, in this "archive", folder, i can hv a backup of my worksheet for each new day that i've created. Thank you!
This is a great tutorial. This trick is going to save me so many thousands of clicks! It's saving my hands my muscles and my mind from permanent damage. Thanks so much!
I learnt so much from your videos, keep using what i learnt in real worklife situation. Although i have so many unresolve things, but Thank you so much for your tutorial
It is so amazing for me. I never felt that vba is so simple to learn and use. Thank you very much.
This is amazingly helpful. I can't believe I didn't know about this before. Thank you! I just subscribed to your channel.
Its you I come back to every time. Thank you very much.
Wow! Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Please keep uploading great videos!!
Mr. Takyar I looked your youtube video "Save Excel file usign data from cells & single click" very useful but I want to go a step further, by given leave existing file as it is and creat new file name as one of cell reference
Sir Thank you very much
I used Now your said idea and found nice results as ok for me , thanks again.
Great 👍
Is it possible to automate the creation of a folder and save the file in the new folder?
Thank you for the video the information helped me tremendously.
Yes. Use the mkdir command. So if you have a folder called mydata in your Hard Disk Drive C, then using
mkdir "C:\mydata\mywork" will create a new folder called mywork in mydata in C.
Hello, first I would like to thank you for such a great and helpful video. I have an issue with 2010 excel, code works and saves the way I would like to see it, however all my formatting erases. I created file in xlsm and added many validation lists, therefore I need to keep my file is this format. Whenever is saves it - file becomes without any formatting. Please help!!!! Thanks a lot in advance.
Hi sirji also try make video on power bi actually working as VBA developer but want to switch as power bi developer if possible please make video on power bi way your teaching is very easy and easily undestandable
OK. Thank you!
Hi, is it possible to save a copy of the same worksheet in a different folder for each new day of work that i have done? So this folder will act as my backup of work for all the days. Thanks!
If you watch the earlier videos especially those dealing with user-forms you'll find a quick solution.
Dinesh Kumar Takyar hi
I want to save my work book as cells
A1_B2_A2
I’m getting data type error and vba is not accepting special characters
Thank you so much for the video. how can i write he code (based on 3 cell ranges) under save as or save without designating the path, and if the cells are blank, it will keep the default name?
Thanks in advance.
Use the standard procedure as shown in the video with an IF condition that if the 3 cells are blank then use the 'default' name.
Hi,
This might be a stupid question, but I'm only trying to use VBA to help me sorting out my administrative tasks in less time (nagging boss). My question is, how can I save only one sheet as a PDF using data from certain cells to form the filename?
Thanks a lot for your wonderful work!
Hi, i have built a worksheet that i use daily but i want to be able to click on a button and have it create a backup copy in another folder for each new workday. Is this possible? So, in this "archive", folder, i can hv a backup of my worksheet for each new day that i've created. Thank you!
Hello, is it possible to save the file as a text file?
Also, is it possible to save only a specific range in that text file instead of the whole workbook? Thanks!
Excellent! Thank you very much.
Hello Sir,All your videos are really awsome and very much useful for a beginner like me.I have a tried this too and works well.But I have a question.I work on an excel sheet by default and copy paste it in each folder that I work.I want to save this file according to a specific name and get it saved in the same folder were I am working in and delete the default file from that particular folder.What can I do for that.
Search www.exceltrainingvideos.com
thanks for nice explanation. I have a question pleas : in MS word 2019, if I need to add a command button inside the word FORM or template, THIS TEMPLATE HASE SOME FIELDS TO FILL IN the fields are all in type plain text content controls. I want to write a code for the command button that save the document as the content of “student name” field (plain text content control), and to save it into a specific bath directory, how can I write the code pleas ? can you drop the line code that do this when I click in this command button.? pleas your answer and thank you in advance. (PS I don’t want to print the command button in paper when I print out the document . can I make the command button unprinted?)
Will work on this.
@@Exceltrainingvideos thank you very much i am wating for you answer it well tech me some thing in macro vba foe word
Thank you for the most valuable info. I have one question. I want to save into multiple files based on each cell value. Is it possible to have more than one file with different names? Thank you for all!
Yes.
Great video! Does it work also for Excel 2016 .xlsm files?
Yes.
@@Exceltrainingvideos thanks for sharing
Hi Sir,
can we do the same for saving web page with a specific name from specific cell as the file name if so please make a video on that this will be helpful to me.
Thanks
Yes.
Super helpful!
Glad it was helpful! Please share the Excel VBA tutorials with your friends.
@Dinesh Kumar Takyar what if you wanted to save it as a PDF, and retain the edit version but next time you open it it changes invoice number
This link should help: www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/#
Also, you can serach at exceltrainingvideos,com
hello, the clips for vba provided in youtube is very helpful, even i have acted as per the Save Excel file using data from cells & single click, Now the problem is i want to save file including the date on which file is saved...want a help
Is there a way to get the save button to only show up in the master file?
My macro gives error if I have a cell value as current date. I want to save my file ending with date taken from a cell. Thank you
if I change .xls whith .xlsx or .xlsm, why the new file is had trouble..
thank you very much! save me some time for the billing! ;)
good day sir! i have a question... how could i retrieve, edit and save a record in excel suing visual basic... thank you
Nice video. I am trying to do the same thing in ms word. Word does not have cells so not sure how to apply it to word. Any ideas? Regards.
Hi,
I have a issue. I am automating a webpage to download a document from it using a vba script. I can click the download option but upon clicking i get a pop below the window like, { open, save as and close}. Alt + S works fine for saving the document. Can you provide me a solution for how to go about it! I have tried using send keys but in vain.
Many thanks Dinesh, very helpful, works perfectly, one question, how can I remove all spurious tab spaces if I am outputting to MS-DOS text file from this process? I am concantenating ; but get a result like this: A04001-0 ; 1061003K instead of: A04001-0;1061003K the file type I am using is: xlTextMSDOS - any thoughts would be much appreciated
Interesting, the tab spaces showed in the editor and disappeared on posting!
Hallo!
thanks for the Video!
i have just a queastion how can i save it when the file it is not in my PC but in a SharePoint in the red?
Ask your system administrator or the SharePoint people.
hey dinesh
i am having a problem saving the file name as a date
do you have any idea how can i do it?
it will be very helpful thanks,
+John Marrelly You have to convert the date to a string as shown in my latest video: ua-cam.com/video/KFmXBgPVJw8/v-deo.html
I have a question and am looking for a solution. I currently have a sheet with numerous option buttons for certain categories. Each computes a total score based on what was selected. How do I save the selected choices without them being effected when I change them in the future? Thanks!
Question not clear.
I have a sheet on excel with option buttons that provides answers to numerous questions. After all option buttons are filled in a total score is calculated for each individual that takes the "test". How do I save each individual's answers without them being changed each time somebody else takes the test? Basically the answers selected for each option button need to be saved for each person to perform the test repeatedly. Thanks.
Or having something similar where if you select the name of the participant in a drop down list, their selected answers on the option buttons would appear.
Can this be applied to ms word?
Is it possible to save as PDF instead of xls.
These ideas on VBA will help:
www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
“C:\Users\takyar\Desktop\test-save.pdf”, OpenAfterPublish:=True
www.exceltrainingvideos.com/how-to-save-microsoft-excel-file-as-pdf-file/
Is it possible to save a copy of an excel file using the data that is in B1 with a date time stamp and save the copy as a read only file? I have a folder on my desktop named data that I want to save it to. I want the file to be called the part number that is in B1. That will change based on the parts but than I want it to have a date formatted yyyy_mm_dd hh mm AM PM and have it as a read only copy. It is so people can go back to see how it was running at a certain time and view the parameters without being able to change them.
Check out these links:
www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/
For read only you'll need to add the 'read-only' flag.
Hello,
I have one question about this video, because I tried to do it like in video but document is not saved and there is no error warning.
Before entering VBA formula I saved document.
So, I want to use one document which is formated in advance and after some datas in cells change I want to save document with this datas,
So all the time I want to have one basic document and after changing data I want to save it with different name.
Please let me know does this works just with unnamed document or can I use it like I described?
Thank you.
+jasmina djuranovic If you wish to have a different file name then the cells that are being used for creating a file name should also change.
I have changed also cells, but its not working.
like I wrote I have made VBA in already saved document. is this reason why its not working?
I can use some help please. I am using Excel 2016. I followed the steps from the video, but in my Excel doc the button isn't "clickable". When I click on it, it like it wants me to edit it(move it). I have enabled the macros per this article: support.office.com/en-us/article/Enable-or-disable-macros-in-Office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6
+Michael Panchesine (Lymen) I figured it out. I was still in design mode.
+Michael Panchesine (Lymen) Super!
could you explain how to add a "Save As" button to one of your worksheets, simply to open up the save as menu
This link will help:
www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
On an activex button control use the following code:
Application.GetSaveAsFilename
Alternatively you can create a macro with the above line of code and assign it to a form-control button.
Maybe you would like to invest in the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
Or visit www.exceltrainingvideos.com to learn more for free.
thank you so much!
Sir,
Instead of defining 3 different filename variables for storing cell values using Range function.
can't we just define one single variable for storing cell values using Range function or ant other function.
Yes, of course.
Hello, I figured out how to save as a text file. Use this: ".txt", FileFormat:=xlText instead of : ".xls", FileFormat:=xlNormal
I would still need to know how to save only a specific range.
Thanks!
Hi Sir, Thanks for your service. my doubt is , How to get a specific values from cells and save as pdf in single click . ?
I want to sort list the values that has save as pdf .. please teach me how sir ?
Sub FileNameAsCellContent()
Dim userPath As String
Dim FileName1 As String
Dim FileName2 As String
userPath = Environ("UserProfile")
'Edit the save location here with first folder location after C:\Users\[user]\ or leave to save to Desktop
Const LOCATION As String = "\Desktop\"
'Select which cells to use in filename here
Application.DisplayAlerts = False
FileName1 = Range("F5")
FileName2 = Range("B6")
With ActiveSheet
'Edit filename format here or keep as default (default text - text.pdf)
PDFFullName = userPath & LOCATION & FileName1 & " - " & FileName2 & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFullName, Quality:=xlQualityStandard, OpenAfterPublish:=False
Application.DisplayAlerts = True
End With
End Sub
Thank you so much
Glad that the video helped.
I have a sheet on excel with option buttons that provides answers to numerous questions. After all option buttons are filled in a total score is calculated for each individual that takes the "test". How do I save each individual's answers without them being changed each time somebody else takes the test? Basically the answers selected for each option button need to be saved for each person to perform the test repeatedly. Thanks.
Save them in an Excel worksheet specific to a student. For example, Aamir's data can be placed in a worksheet named 'Aamir'.
Thank you I appreciate the help! Is there any way to connect those option buttons to a combo box or drop down list with the students names to keep it all in one excel file rather than saving an excel document for each student
Sir,
Greeting of the Day!
I am doing accounts work. mu question is ,,,,
Example- when I made a profit and loss ac in excel 2007 for the mo april-15 and the next month i will change only month name and figures.I want that both months figures add and save on next sheet.
Hi, Can U pls Tell me How to Save a Particular Cell Range Instead of Entire WorkBook? Pls
Hi,
I'm trying to save an excel file with data from 4 cells.
One of these cells contains a date format "year-month-date".
Can this be used to make this save button work?
I manage it without that 1 date cell using the other 3 without a problem.
Thanks
FYI:
Cell 1: M
Cell 2: 160101 (date cell with custom date format)
Cell 3: D
Cell 4: 23
Ideally this would save as M160101D23
+Capt Med Did you try it out? Shouldn't be a problem. If you have a problem, convert the date to text before using it as shown here: www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/
+Dinesh Kumar Takyar
Hi D, sorry, I'm a total new comer to VBA.
I guess I have it in the wrong order.
Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String
FileName1 = Range("I7")
Dim Path As String, mydate As String
mydate = Range("J7")
mydate = Format(mydate, "yymmdd")
FileName2 = Range("K7")
FileName3 = Range("L7")
ActiveWorkbook.SaveAs FileName:=FileName1 & mydate & FileName2 & FileName3 & ".xls", FileFormat:=xlNormal
End Sub
the macro works. but i got a problem after the save. i use excel 2010. the file format is .xlsx. so i change your code on the bottom line, from FileName2 & ".xls" to FileName2 & ".xlsx". after clik the save button,everything is oke, but when i want to open the file, it said the file is corrupted.
any idea how to solve this?
Zunaidi Then Try the code without changing it.
Yes. I tried it without changing anything. It works perfect. But i got some file that i want it to be saved in .xlsx file format. The reason is, inside that xlsx file,there is some formula that can only work in xlsx format. If i use xls format, the formula won't work and it wil bw #### or #Value!# (something like that)
Zunaidi Then
xlNORMAL; is the command that needs to be changed for this I believe. xlOpenXMLWorkbook
i want to save my active sheet in PDF format with a save button which i have assigned a macro. is it possible.?
Amit Gunjariya
ChDir "C:\Users\takyar\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\takyar\Desktop\test-save.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
If You Don't Mind,
Your Workbook Download Link,
www.familycomputerclub.com/save-excel-files-automatically-using-cell-data.html
Hi Dinesh Kumar,
I've a query!
Let me store Date(Jan 2019 to Dec 2019) as a column in an excel, and i have get each row of the excel input and store in next next new word document automatically
Is this possible ?
For Example :
In Excel:
File name: Sheet 1
Date( Column)
01/ 01/2020
02/01/2020
03/01/2020
In word:
File name: 01/01/2020
01/01/2020
File name: 02/01/2020
03/01/2020
File name: 03/01/2020
03/01/2020
Will work on this idea.
Sir how to remain on active sheet After run this macro
This link will help: www.exceltrainingvideos.com/invoice-tracker/
Hi Sir please help me to save data in specific path and it will vary based on employee ID say EID is 10001 in india folder and EID 10002 in Buthan folder in single click upload the video for the same
When opening . xlsx file it says file format not supported, why so?
We are writing a macro with (.xlsm) to save a (.xlsx) file.
Using excel 2010
Share your code.
use this pal, the setting the workbook name as something else will avoid crashes and the file format down at the bottom should be the correct one for macro enabled documents
Set wb = ThisWorkbook
wb.SaveAs Filename:=Path & Filename1 & "_" & Filename2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Very Good! thanks.
Sir can you help me? please.., save file using data from content control.
Why don't you try first and show me the code?
Hello, I followed your instructions on this video to Save the Excel file using data from a particular cell on the workbook by creating the Active X button. I wrote the code in VBA the same way as you and the button works but after I press the SaveFile button I get an error. The error says "compatibility checker" and "minor loss of fidelity". I believe this is because I am using Excel 2013. Should my line of code with fileformat be different than yours in order to correct this? This is what I have now: Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Path = "I:\1RMWQUOTEFORMS"
FileName1 = Range("H1")
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & ".xls", FileFormat:=xlNormal
End SubThank you!!
+Tommy Shinn Change this line of code: Path = "I:\1RMWQUOTEFORMS" to Path = "I:\1RMWQUOTEFORMS\"
Notice how a small error can cause such a massive effect!
+Dinesh Kumar Takyar Yes!! Thank you very much, it is now working correctly! One more question, it is saving as a 97-2003 file format ".xls" I am using Excel 2013, what would I have to change in order to save it as a ".xlsx" file format so I am not running in compatibility mode? Thank you!
+Tommy Shinn ActiveWorkbook.SaveAs Filename:=Path & FileName1 & ".xlsx", FileFormat:=xlNormal
Ops, how would I save it as a PDF??
Dominic Abrams www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
What would i delete if i dont want to send it though just save it.
Many Thanks
this is my formula so far its not saving as a pdf
Private Sub CommandButton1_Click()
Dim Path As String
Dim Filename1 As String
Path = "C:\Users\abram\OneDrive\Abrams Electrical Services Pty Ltd\Quotes and Invoices\Completed Jobs and paid"
Filename1 = Range("G10")
Filename2 = Range("B7")
Filename3 = Range("C7")
ActiveWorkbook.SaveAs Filename:=Filename1 & "-" & Filename2 & "-" & Filename3 & "pdf", FileFormat:=xlNormal
End Sub
so not just a simple change at the end with FileFormat: pdf
Run time error 1004 displayed... Method 'SaveAs' of Object '_Workbook' failed...
Nice video but to bad when i close my excel and reopen the button disappears, help me
Check your code again.
Thank-you!
You're welcome! Please share the VBA training tutorial with your friends also on social media.
The macro should be out of the workbook to make it usable on other WBs...
?
Works perfectly with Excel 2013 but does not work when I open on another computer with Excel 2007.
This video was made using Excel 2007!
Dinesh Kumar Takyar thank you so very much for your reply. The “form” I used this with is on a shared hard drive. Works perfectly on my computer but when I open on others it doesn’t. Maybe I need to change something ?
Sir could you please help me getting my request as I am trying to figure it longtime. can you please reply with your email so I can send my Excel file.
I have a really good code done by me from different videos which actually save as PDF and send the list of sales for each Individual speratly. but problem is I couldn't figure out to send as Excel file. currently it's sending as a PDF. using the formula of exportasfixedformat xltypepdf
could you help me a code to get a sheet5.activesheet to be send as a Excel. it's auto attached to mail even.
This link will help: www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
Or search www.exceltrainingvideos.com
Any clue why it doesn't work?
Excellent question! You can try this code attached to a command button. Let me know if the code solved the problem:
Private Sub CommandButton1_Click()
Dim Path As String
Dim mydate As String
Dim FileName1 As String
Dim FileName2 As String
Path = "C:\inventory\"
FileName1 = Range("A1")
mydate = Range("B1").Value
mydate = Format(mydate, "mm_dd_yyyy")
FileName2 = mydate
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & ".xls", FileFormat:=xlNormal
End Sub
How to save excel files having the same name but numbered 1,2,3,4, etc. in one folder (with the same name as the files )automatically without having to choose a folder?
How to save file name as name in the worksheet automatically for 30 records
Query not clear.
How can the current time and date be added to the file name? eg, yyyymmddhhmmss-A1-B1.
David Brown Another question; is there a work-around if the cell A1 or B1 in the example has a function assigned or they are protected cells?
At first, the video is great and the formula works! Thank you very much!! =)
Second, I have a question:
Could I save just 1 sheet (not entire workbook).
In my case my workbook have 3 worksheets.
1st sheet is for clients with final prices,
2nd sheet is for the company with calculated costs
3rd sheet have prices for final prices and for calculating costs.
I would like to make a 2nd BUTTON to save as: costumer only or company only...
hope you got me and you can help me
p.s. i tried to kick in the formula AciveSheet but code say error =)
Andi Bertok
Problem solved! Found your video similat ot this and i combined 2 codes =)
Private Sub PFDgumb_Click()
FileName1 = Range("A4")
FileName2 = Range("A5")
FileName3 = Range("A3")
FileName4 = Range("A5")
FileName5 = Range("J24")
ChDir "C:\Users\Andi\Desktop\ESEL\pdf\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Andi\Desktop\ESEL\pdf\" & FileName1 & FileName2 & FileName3 & FileName4 & FileName5 & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub
I have updated this to work with Excel 2019 and to create a PDF:
Sub FileNameAsCellContent()
Dim userPath As String
Dim FileName1 As String
Dim FileName2 As String
userPath = Environ("UserProfile")
'Edit the save location here with first folder location after C:\Users\[user]\ or leave to save to Desktop
Const LOCATION As String = "\Desktop\"
'Select which cells to use in filename here
Application.DisplayAlerts = False
FileName1 = Range("F5")
FileName2 = Range("B6")
With ActiveSheet
'Edit filename format here or keep as default (default text - text.pdf)
PDFFullName = userPath & LOCATION & FileName1 & " - " & FileName2 & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFullName, Quality:=xlQualityStandard, OpenAfterPublish:=False
Application.DisplayAlerts = True
End With
End Sub
Great! All the best!
@@Exceltrainingvideos Thank you Mr. Takyar!
even though i copy your code EXACTLY what you do it doesnt work. enough for me to give up. i have followed at least 6 videos to try to copy EXACTLY what you do and not one has worked - Unbelievable. you videos are clear and i can follow along. oh well not for me i guess.
Copy and paste doesn't work. After pasting the code you should check it line by line and you'll detect a few 'funny' errors or characters. Correct them and you'll smile again!
this is very useful if it worked can someone help me out
Dim Path As String
Dim filename1 As String
Dim filename2 As String
Path = "C:\Users\dominic\OneDrive\Documents\Abrams Electrical Services\Abrams Electrical Services Pty Ltd\Quotes and Invoices\Unpaid Invoices"
filename1 = Range("G10")
filename2 = Range("B10")
ActiveWorkbook.SaveAs Filename: = Path & fileName1 & "-" & FileName2 & ".xls", FileFormat:xlNormal
Dominic Abrams ActiveWorkbook.SaveAs Filename: = Path & fileName1 & "-" & fileName2 & ".xls", FileFormat: xlNormal
Dominic Abrams also what is a VB project? it wont let me save.. says " VB projects and XLM sheets cannot be saved in a macro-free workbook." What does this mean?
Hi, i have built a worksheet that i use daily but i want to be able to click on a button and have it create a backup copy in another folder for each new workday. Is this possible? So, in this "archive", folder, i can hv a backup of my worksheet for each new day that i've created. Thank you!
it worked but im getting the path name also, how can i only have the name of the range a selected instead of path & cell range data?
Thanks a lot
Most welcome