I went through this whole series and it was so well explained and easy to implement. It was my first time working with VBA and I was able to understand each of the steps you made, follow along in my own sheet and debug where needed Thanks!
wo wo,i have actually learnt alot from your videos,i was planning on taking a computer short course at a cafe,but now i know alot that its even helping in planning my company work...thanks alot again\ from zambia
If you are having trouble with your logo being deleted use this code: For Each shp In ActiveSheet.Shapes If shp.Type msoPicture Then shp.Delete Next shp
Briliant series. Thank you so much. I have never used VBA, but I could follow along and you've helped me so much with the record keeping for our (very) small business. I can't thank you enough.
Thanks Chester, a great series well explained. I am new to excel and I basically did everything you said and it worked. I even left things out that I didn't think I´d need and the program still worked. Exactly what I needed. Thanks again.
Great explanation Video. i seem to be struggling with Run-time error 1004 code. wondered if you have experienced this at all and could help? Thanks in advance
I really enjoy your videos Chester, one question, instead of making 2 different buttons to save as PDF and as new Excel Workbook, can we not just put them both on the same macro ..?
Hello Chester! This is by far the easiest tutorial I have followed and thank you for sharing, you are brilliant!❤ Can I ask if the path for saving the xls and the pdf can also work if the path is located in teams folder instead of my personal folder? In that way anyone who will generate an invoice will get it saved on just 1 specific path. Thank you and happy new year!!! All the best!
As previously mentioned, very good step-by-step instructions. One question I do have is, is it possible to have the records on sheet 3 entered once whilst using 2 or more buttons? So when I save as xlsx and pdf, I only have one row in sheet 3 (for arguments sake) thanks
Great tutorial, thank you! I have a question: I would like to have the file name as Date & Time, which are in cells C5 and C6. Currently using fname As String I'm getting a number (ex. 0.625) rather than "11-11-2024 7:00 PM", which is the desired outcome.
Thank you for the great videos, I followed all the steps, changing some cells following my invoice format. It's amazing and will help me in my small business. I have one concern, when I'm adding to the invoice record, it is saving the same info in 3 different rows: Row 1- without the xlsx and the pdf files Row 2- including the xlsx file and without the pdf Row 3- including the pdf file and without the xlsx Please how can I make add the invoice record all in the same Row? Thank you 🙏
if you need to save the same record in 1 line but with 2 different files, which is in excel and PDF, you have to do combine some of code 'save the new workbook to a specified folder
With ActiveWorkbook .Sheets(1).Name = invno .SaveAs Filename:=path & fname, FileFormat:=51 .Close End With ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname Set nextrec = Sheet9.Range("A1048576").End(xlUp).Offset(1, 0) nextrec = invno nextrec.Offset(0, 1) = idno nextrec.Offset(0, 2) = custname nextrec.Offset(0, 3) = amt nextrec.Offset(0, 4) = dt_issue nextrec.Offset(0, 5) = dt_issue + term
@@riisma Thank you for the tips! It works! To point out to everyone, add this code to the previous code (SaveToExcel) by Chester. Enter it right after the line of codes to remove shapes/buttons. Also, do remember to replace the above "Sheet9" to whatever sheet number you need.
@Chester thanks for the tutorials, their most helpful! I'm struggling with a couple of issues with the "Save as .xlsx" and "Save as PDF" macros: first off, everytime I try to save a new .xlsx file an error prompt requires me to delete and rewrite the "custname = Range("B10")" variable; the second issue, after I'm unable to create a "Save as PDF" I get a "Run-time error '1004' message, and when I click on the debug button Excel highlights the following command line: "ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname". I'd appreciate any assistance you can provide to solve this.
Very nice, clear instructions and I was able to tweak some things for my needs. However, the buttons are copied to a second pdf page. This can be avoided by using "To:=1" (assuming the invoice is a single page). I also used the parameter "OpenAfterPublish:=True" to immediately see the file after it is created.
Hi, love your vidoes! My filename has special characters in it ( - and : ). What macros would you use to eliminate the special characters so that I can save the file?
I want to select a template from a list of templates and fill up the forms accordingly. Also want to attach some photos in the sheet before exporting. Can the code be used in a dynamic way depending upon my fields?? Please make a video on that. This video teaches a lot. Thanks a lot.
Thank you for thiis video series! This is the first time I have used mcaros/VBA and your videos were very easy to follow. I do have one question in regards to saving as .pdf. I have set my invoice to be saved in sharepoint, so the filepath uses my username to get to the desired folder. I would like my coworkers to be able to use the same invoice template and have the invoices they submit go to the same folder. Is there a way to change the code so that any user's invoice will go to the same folder in sharepoint while using the same invoice template?
Hey Chester love your work! Question, I have 7 worksheets (1 is invoice and other 6 are terms and conditions). How can I edit the VBA script to export those sheets and save them in the same pdf?
G'day Chester, I have been following and using your tutorials with great success thank you, I have been asked if I could make the Record of invoices sortable, yep easy I thought just make the sheet into a table, man was I wrong, I have 2 questions 1. Can it be done, and 2. do you have a tutorial on how to do it.
when I save the invoice to an Excel sheet I have to create the button I'm losing my logo. can I understand why? Thank you so much for this wonderful series.
Hi Chester! These videos are incredibly useful for me and my company. thank you for that :) I only have 1 problem: when I want to run the "save as PDF" macro. My pdf-file is only being sent to a printer queue, and not to my documents on my computer. Any advice on how to bypass this? I'm working on a macbook I should note.
Hi Chester, thank you for this, I use it monthly. Just a quick question: if my description box and amount is identical across my client database (because the service amount is the same for everyone), how do I create a new macro that allows me to click a button and generate all the invoices at once, in PDF for my entire database? hope that makes sense.
Great advice and I am adapting the codes for my applications at work, however I have hit a run-time error code 424, on the nextrec.offset(0, 1) = tyofwor (tyofwo meaning type of work), I have the same problem in using the code to record to file with the hyperlink. If I delete that section of the code, the details are recorded on the record minus the hyperlinks to the files, xlsx & pdf are you able to advise where I might be going wrong?
Well explained series but two of my commands don't work, one in saving in PDF and the other in saving as excel. For the excel one it export it to new workbook but doesn't save it in the Path, and for the PDF one gives me error #1004, although the spelling and all other points matches with the video, I had to suspend them in order to continue. In both cases invoice and the path and file name add to Record of Invoices. Is anybody knows what the problem might be? Thank you.
Hi Chris, pls help. Even though I specify the path, it saves in a different folder. On the record sheet it displays the correct path as the link details.
Everything seems to work well until I get to the "activesheet". It continues to ask me to debug, but I'm not sure what I'm doing wrong. Any suggestions?
Thank you for some great videos :D I have made the pdf file name starting with date and then a name, but how do i make that date start with YYYY-MM-DD instead of DD-MM-YYYY?
Thanks Chester! I have a problem with export to pdf, can you please help with this error. It says - Run time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving. When I click on debug it highlights the following command. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & fname, IgnorePrintAreas:=False, OpenAfterPublish:=True P.s. I use Microsoft office 365. Thanks in advance. :)
I have been able to create invoice, and the first two buttons but when it comes to saving the items to excel or pdf files I keep getting an error. Working on a mac it highlights an error with this line: activesheet.Exportasfixedformat Type:=xltypePDF, ignoreprintareas:=False, FileName:=path & fname
My exported PDF was pagebreak into other pages because of hidden columns and rows, for example hidden column B-E, hence column A one page, column F another page, is there any code that can fix this?
Could someone help me with an error I keep getting? The error occurs when I press the [Save as . xlsx button] The error says: "(My file name).xlsm'!saveinv'. The Macro may not be available in this workbook or all macros..... All other buttons work correctly.
Hi Chester, another question; just finished Save Invoice Worksheet as PDF and I've copied literally all your code to my VB on my Macintosh. Mine prints the file instead of doing what you showed in your tutorial. Any thoughts?
Is there away to have the hyperlink to the pdf and excel files appear on the same line in the record of invoices instead of a whole new line being created each time? Please help 😅
Is there a way that you can record the invoice saved as excel and invoice saved as PDF on the same record line in the Record Of Invoices worksheet? It seems to creat a new record line for both of these actions for the same invoice number
@@TCThompson74 looking at it, i assume the end and offset command look at the pdf/excel column. The first blank is filled in, which should be the correct one. So long as the offset is correct, it should work. I haven't tried it but just my quick thoughts
Outstanding set of videos. Well organized. Well Timed. Well Presented. Using the code provided in the video, I attempted this macro. The code below creates a PDF to the desktop but, despite hours of iterations, I cannot get the filename to save as the data from the cell. The filename is always the name of the workbook. Any help provided would be much appreciated Sub SaveAsPDF()
Dim wks As Worksheet Set wks = ActiveSheet Dim Path As String Path = "Users\keithfrost\Desktop\" Dim filename As String filename = wks.Range("F4").Value ActiveSheet.ExportAsFixedFormat Type:=TypePDF
I keep getting "variable not undefined" message for "dt_issue = range{"C3"). I have not recieved this with the previous variable as I have followed your series. How do I address this?
G'day Joan, I am not sure if this will help but I noticed 2 possible problems,(if you copied & pasted the code) there is a " at the beginning of the code, and the first (open) bracket is a { rather than a ( hopefully this won't help because you managed to get it sorted.
I went through this whole series and it was so well explained and easy to implement. It was my first time working with VBA and I was able to understand each of the steps you made, follow along in my own sheet and debug where needed Thanks!
I just finished watching all the series and made me understand the whole potential Excel has. GREAT VIDEOS!
I went through the whole series and everything lived to its billing. Many thanks for making such knowledge available.
Great to hear!
wo wo,i have actually learnt alot from your videos,i was planning on taking a computer short course at a cafe,but now i know alot that its even helping in planning my company work...thanks alot again\
from zambia
If you are having trouble with your logo being deleted use this code:
For Each shp In ActiveSheet.Shapes
If shp.Type msoPicture Then shp.Delete
Next shp
This works perfectly! Thanks.
Briliant series. Thank you so much. I have never used VBA, but I could follow along and you've helped me so much with the record keeping for our (very) small business. I can't thank you enough.
That's great to hear - all the best with your business!!!
Thank you sir for your tutorial. Now I can manage my documentation properly. It helps a lot. Thank you sir.
More VBA invoice project goodness! Thanks Chester!!
IT'S NICE AND I WANT ONE FULL WORKSHEET AS YOU SHOWED ABOVE 5 OPTIONS INCLUDING CUSTOMERS AND RECORDS OF INVOICE SHEETS
You're the man, Chester Tugwell! Thank you!!
Thanks Chester, a great series well explained. I am new to excel and I basically did everything you said and it worked. I even left things out that I didn't think I´d need and the program still worked. Exactly what I needed. Thanks again.
Great explanation Video. i seem to be struggling with Run-time error 1004 code. wondered if you have experienced this at all and could help? Thanks in advance
I really enjoy your videos Chester, one question, instead of making 2 different buttons to save as PDF and as new Excel Workbook, can we not just put them both on the same macro ..?
Hi Chester, so helpful. Do you cover how to have the buttons on a separate sheet? I don't want the buttons on the form sheet.
Hello Chester! This is by far the easiest tutorial I have followed and thank you for sharing, you are brilliant!❤ Can I ask if the path for saving the xls and the pdf can also work if the path is located in teams folder instead of my personal folder? In that way anyone who will generate an invoice will get it saved on just 1 specific path. Thank you and happy new year!!! All the best!
Hi, I have exactly the same question. Have you by any chance found to answer by now?
As previously mentioned, very good step-by-step instructions. One question I do have is, is it possible to have the records on sheet 3 entered once whilst using 2 or more buttons? So when I save as xlsx and pdf, I only have one row in sheet 3 (for arguments sake) thanks
Great tutorial, thank you! I have a question:
I would like to have the file name as Date & Time, which are in cells C5 and C6. Currently using fname As String I'm getting a number (ex. 0.625) rather than "11-11-2024 7:00 PM", which is the desired outcome.
Thank you for the great videos, I followed all the steps, changing some cells following my invoice format. It's amazing and will help me in my small business.
I have one concern, when I'm adding to the invoice record, it is saving the same info in 3 different rows:
Row 1- without the xlsx and the pdf files
Row 2- including the xlsx file and without the pdf
Row 3- including the pdf file and without the xlsx
Please how can I make add the invoice record all in the same Row?
Thank you 🙏
did you ever figure this out? As I'd love to know!
if you need to save the same record in 1 line but with 2 different files, which is in excel and PDF, you have to do combine some of code
'save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(1).Name = invno
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname
Set nextrec = Sheet9.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = idno
nextrec.Offset(0, 2) = custname
nextrec.Offset(0, 3) = amt
nextrec.Offset(0, 4) = dt_issue
nextrec.Offset(0, 5) = dt_issue + term
Sheet9.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".pdf"
Sheet9.Hyperlinks.Add anchor:=nextrec.Offset(0, 8), Address:=path & fname & ".xlsx"
@@riisma Thank you for the tips! It works! To point out to everyone, add this code to the previous code (SaveToExcel) by Chester. Enter it right after the line of codes to remove shapes/buttons. Also, do remember to replace the above "Sheet9" to whatever sheet number you need.
Thanks. Great Videos, One thing asked that, if Invoice Number is combine of Letter with number such as MZ-OO1 Then what to do
@Chester thanks for the tutorials, their most helpful! I'm struggling with a couple of issues with the "Save as .xlsx" and "Save as PDF" macros: first off, everytime I try to save a new .xlsx file an error prompt requires me to delete and rewrite the "custname = Range("B10")" variable; the second issue, after I'm unable to create a "Save as PDF" I get a "Run-time error '1004' message, and when I click on the debug button Excel highlights the following command line: "ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname". I'd appreciate any assistance you can provide to solve this.
Very nice, clear instructions and I was able to tweak some things for my needs. However, the buttons are copied to a second pdf page. This can be avoided by using "To:=1" (assuming the invoice is a single page). I also used the parameter "OpenAfterPublish:=True" to immediately see the file after it is created.
Hi, love your vidoes! My filename has special characters in it ( - and : ). What macros would you use to eliminate the special characters so that I can save the file?
I want to select a template from a list of templates and fill up the forms accordingly. Also want to attach some photos in the sheet before exporting. Can the code be used in a dynamic way depending upon my fields?? Please make a video on that. This video teaches a lot. Thanks a lot.
Thank you for thiis video series! This is the first time I have used mcaros/VBA and your videos were very easy to follow. I do have one question in regards to saving as .pdf. I have set my invoice to be saved in sharepoint, so the filepath uses my username to get to the desired folder. I would like my coworkers to be able to use the same invoice template and have the invoices they submit go to the same folder. Is there a way to change the code so that any user's invoice will go to the same folder in sharepoint while using the same invoice template?
Hi, I have exactly the same question. Have you by any chance found to answer by now?
Simple code and it works!! Thank you for the help Chester!!
Can I combine the macros you made so that everything populates in 1 row on the record of invoices sheet, and not in separate rows?
Thank you so so much for this series!! It was extremely useful :)
Thank you very much for these videos!
Hey Chester love your work! Question, I have 7 worksheets (1 is invoice and other 6 are terms and conditions). How can I edit the VBA script to export those sheets and save them in the same pdf?
Thank you sir.. Is there an idea to hide the blank lines of the invoice when printing it via vba codes?
G'day Chester, I have been following and using your tutorials with great success thank you, I have been asked if I could make the Record of invoices sortable, yep easy I thought just make the sheet into a table, man was I wrong, I have 2 questions 1. Can it be done, and 2. do you have a tutorial on how to do it.
when I save the invoice to an Excel sheet I have to create the button I'm losing my logo. can I understand why? Thank you so much for this wonderful series.
Hi Chester! These videos are incredibly useful for me and my company. thank you for that :) I only have 1 problem: when I want to run the "save as PDF" macro. My pdf-file is only being sent to a printer queue, and not to my documents on my computer. Any advice on how to bypass this? I'm working on a macbook I should note.
I'm having the same problem on mac T^T
I have same problem on mac. Do you know how to fix?@@haileylee3236
I just fix it.
Hi, thanks for this.
as far as you know, will I be able to build the same on Libreoffice, as far as VBA and macros go? Thanks
Very nice tutorial, however what if i'd like to save my pdf inside a custom folder reference to the path. Appreciate your kind response.
timeless. Thank you!
Your videos are very very helpful, God Bless you!
So now when I remove the shps from the copied form it removes my logo :-( what is the work around?
This is great! Thank you for being soo helpful
how to export two or more sheets in PDF by one command with cell value in a specific folder?
Hi Chester, thank you for this, I use it monthly. Just a quick question: if my description box and amount is identical across my client database (because the service amount is the same for everyone), how do I create a new macro that allows me to click a button and generate all the invoices at once, in PDF for my entire database? hope that makes sense.
Great advice and I am adapting the codes for my applications at work, however I have hit a run-time error code 424, on the nextrec.offset(0, 1) = tyofwor (tyofwo meaning type of work), I have the same problem in using the code to record to file with the hyperlink.
If I delete that section of the code, the details are recorded on the record minus the hyperlinks to the files, xlsx & pdf
are you able to advise where I might be going wrong?
Were you able to figure this out?
any solution for macbook. i have set my export to pdf button for windows, but wont worknon mac. Any propositions or solution?
Well explained series but two of my commands don't work, one in saving in PDF and the other in saving as excel. For the excel one it export it to new workbook but doesn't save it in the Path, and for the PDF one gives me error #1004, although the spelling and all other points matches with the video, I had to suspend them in order to continue. In both cases invoice and the path and file name add to Record of Invoices. Is anybody knows what the problem might be? Thank you.
Hi Chris, pls help. Even though I specify the path, it saves in a different folder.
On the record sheet it displays the correct path as the link details.
Evening, First time using excel, VBA or Macros. I am having trouble getting my macro to run. Who can I contact for help getting it to work properly
Hello, thank you for this video, is it works in with $ too?
Everything seems to work well until I get to the "activesheet". It continues to ask me to debug, but I'm not sure what I'm doing wrong. Any suggestions?
Thank you for some great videos :D I have made the pdf file name starting with date and then a name, but how do i make that date start with YYYY-MM-DD instead of DD-MM-YYYY?
Thanks Chester! I have a problem with export to pdf, can you please help with this error. It says - Run time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving. When I click on debug it highlights the following command.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & fname, IgnorePrintAreas:=False, OpenAfterPublish:=True
P.s. I use Microsoft office 365.
Thanks in advance. :)
I have been able to create invoice, and the first two buttons but when it comes to saving the items to excel or pdf files I keep getting an error. Working on a mac it highlights an error with this line: activesheet.Exportasfixedformat Type:=xltypePDF, ignoreprintareas:=False, FileName:=path & fname
Running into the same! Did you happen to figure this out?
Hi, did you manage to find a fix for your issue, I'm experiencing the same issue :)
My exported PDF was pagebreak into other pages because of hidden columns and rows, for example hidden column B-E, hence column A one page, column F another page, is there any code that can fix this?
Could someone help me with an error I keep getting?
The error occurs when I press the [Save as . xlsx button]
The error says: "(My file name).xlsm'!saveinv'. The Macro may not be available in this workbook or all macros.....
All other buttons work correctly.
Subcribed! Super thanks!
Hi Chester, another question; just finished Save Invoice Worksheet as PDF and I've copied literally all your code to my VB on my Macintosh. Mine prints the file instead of doing what you showed in your tutorial. Any thoughts?
Kindly tell me the version of excel ur were teaching us
Can you have a button that will save as xls and pdf combined ?
What if the file path is based on a cell value. Please show what vba code I should use. Thank you.
Is there away to have the hyperlink to the pdf and excel files appear on the same line in the record of invoices instead of a whole new line being created each time? Please help 😅
Is there a way that you can record the invoice saved as excel and invoice saved as PDF on the same record line in the Record Of Invoices worksheet? It seems to creat a new record line for both of these actions for the same invoice number
I'm having the same "issue". Anyone know the answer? (Great videos by the way! I feel so efficient!)
@@TCThompson74 looking at it, i assume the end and offset command look at the pdf/excel column.
The first blank is filled in, which should be the correct one.
So long as the offset is correct, it should work.
I haven't tried it but just my quick thoughts
@@Avvura please can you explain further 😂 I can’t figure this out
@@kanekhan3424 I also want to understand.
@@Avvura Please explain in detail, if you don't mind.
Where it says " 'your path here " on the macro. How do I know what to write?
The other two codes ran on mac but not this one. Any suggestions?
Just wow,,Incredible
Then there at that default email,how to make like two or more lines.thanks
thanks bro, it works,
Outstanding set of videos. Well organized. Well Timed. Well Presented.
Using the code provided in the video, I attempted this macro. The code below creates a PDF to the desktop but, despite hours of iterations, I cannot get the filename to save as the data from the cell. The filename is always the name of the workbook.
Any help provided would be much appreciated
Sub SaveAsPDF()
Dim wks As Worksheet
Set wks = ActiveSheet
Dim Path As String
Path = "Users\keithfrost\Desktop\"
Dim filename As String
filename = wks.Range("F4").Value
ActiveSheet.ExportAsFixedFormat Type:=TypePDF
End Sub
I keep getting "variable not undefined" message for "dt_issue = range{"C3"). I have not recieved this with the previous variable as I have followed your series. How do I address this?
G'day Joan, I am not sure if this will help but I noticed 2 possible problems,(if you copied & pasted the code) there is a " at the beginning of the code, and the first (open) bracket is a { rather than a ( hopefully this won't help because you managed to get it sorted.
I am getting error in this code (ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & Fname)
It was working, then it stopped yesterday. What was the fix?
I get the same error as well.
Is there a way to have both, excel and PDF hyperlinks show on one (the same) line on the record of invoices?
Don’t suppose you figured this out?
Dear Sir, i am trying to save as pdf the same way but facing error 1004, can you please help to resolve it Thank you
Can i buy the vba template ?
for some reason there's a bug in this:
ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, ignoreprintareas:=False, Filename:=path & fname
doesn't work in mine
while converting to pdf windows popups "PUBLISHING" i do not want this window to appear please guide
Just add line at the end "OpenAfterPublish:=False" without the quotes
Thanks
Welcome
How match debit credit amount with vba
My advice don't follow those videos if you are using Excel 2007, some of the stuff don't work