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
I'm like this guy has been so great its gotta be in the comments ( AMAZING ) !! Just got rid of quick books wayyyy to costly thanks so very much from all of us !
I have found these videos to be absolutely brilliant. There seems to be an issue with excel on MAC when trying to go through the process of converting the document to a PDF - it wasn't a necessary function for me though. The only other problem is that I have to have the original document open whenever I open other excel documents, otherwise it does not recognise reference points for the customer database. Greats videos and very educational for anyone who isn't confident with macros.
Chester, you are a ROCKSTAR for doing these videos. Wow, you have overhauled my entire invoicing system and given me a massive crash course in the wonders of Excel. Thank you!
Hi Chester, I was pretty green to Excel when I found your series and found it very useful as I was asked to create just a simple invoice form to be typed up, however I stumbled upon you video and followed your instructions modifying to their needs. I added a small form to sheet1 on the right hand side to easily add customer details inc phone, mobile and included formatting of phone and mobile numbers to make them more readable and a check for number of digits. also used PROPER to enable quicker typing in of name and addresses along with a macro to add to customers sheet2. Also after showing to the recipient there where two requests, 1. to have more space to add items on some invoices, so I added a longer form and two macros for a 'One page Invoice' and a 'Two page Invoice,' the One page Invoice macro button Hides cells in the middle part of the form and the Two page Invoice button Unhides the same cells. 2. Which I haven't found a way of doing as yet is to be able to re-open an edit (add to) an Invoice after another has been started, this is because many of there jobs are awaiting parts which often get delayed. I did think maybe adding an incremental version number to the saved filename or something, Any ideas on how this could be done?
Chester! Great Job I liked your teaching style. Thank you for that, I have one creation of PDF saving file but will also look into creating what you just teach herein Good Job Sr.
Thank you so much for making this video it has made my life easy I just started a smell coming and your video really help me actually it’s saved my lot of time it’s very easy
If you are on a mac, and if you end up with the filetype saving as a "Document" despite declaring the filetype to be a 51 (xlsx?) type. You can replace the following line: .SaveAs FileName:=path & fname, FileFormat:=51 with this one: .SaveAs FileName:=path & fname & ".xlsx", FileFormat:=xlOpenXMLWorkbook Now all of my files save as .xlsx files and my mac opens them up with excel by default when I double click. Hope I did it right. I'm really new to this so please correct me if I'm wrong :)
I want to thank for this tutorial, it is awesome and works just fine! Now, I know very little of Excel and nothing about VBA, could someone tell me how to modify the code to save the file using an specific cell from the template?
Hi Chester, Your videos are amazing as ever. I seem to be having an issue with my invoice number, its always pulling through as 0? I've tried changing the formatting but to no avail? Please help
Hello, Thanks for the great video's it helps me a lot. After i do all if it what you say in the video it works perfect only when i push the hyperlink he cant open my xlsx, can you tell me what i can do with it so he will open it. Thank you
Dear Chester. Thank you for these tutorials. Very helpful. However, i have had trouble with the latest video in order to store the file as a normal excel file. I have used the fileformat:=51 code in the macro but when i play this, it saves as an unrecognised "A File"? I have double checked all the code and this is what appears to be the problem. ANy suggestions?
Hello Chester, It is a great series from your side. Greatly appreciate your efforts in compiling and coaching the invoice automations. Just one clarification is needed. Whenever I save the new file in the folder and open it, I found that the VBA modules are not copied. I have also mentioned in the template file VBA module as .SaveAs Filename:=Path & FName, FileFormat:=52, which should allow the file to be saved as xlsm and retain the VBA modules. I require this way because I have SpellNumber VBA working in an Invoice cell. Can you please tell me where I may be going wrong, Please ?
Hi Chester! Thanks for your invoice series. I am new to VBA but with your help I have done a invoice generation excel with all the features you have explained and now I am getting a message while saving the invoice as normal xls and it is asking for confirmation with these option - SAVE, SAVE AND ERASE FEATURE, GO BACK, HELP - Please help to avoid this situation. Thanks a lot. Regards Abhi
This series has saved me! I cancelled by Self Employed QuickBooks subscriptions. The only issue I'm having is when I save the colors in the formatting changes to a darker color from the original. Any way to fix that?
Hello Chester, thank you for the great tutorial. I am very new to the VBA and I find it really helpful and easy to follow. I face one problem. When I try to save the file a message 1004 appears, about the use of special characters (although I don't use any). Can you please help me with this?
Hi Chester, great videos :) Just a quick one is there a way of changing the location of a saved file? We work in job numbers and each job has its own folder i.e J24001, J24002 etc so in the save as xlxs macro I would like to save each invoice to a separate folder, is this possible?
Hi Chester, I felt like a superstar after being able to build the document from scratch following your videos, now I am facing an issue, I had this document saved on my computer with the paths also to my computer, Today I by accident had everything synced to Onedrive and for this reason nothing more is working. Is there any way out of this issue?
Sir, can we add two macros in single button like 'save as PDF and save as Excel file' in one button? because when i clicking these buttons they are creating hyperlinks to different next row so, make it like both these hyperlink in same row . plz
Have gone through all of the videos in this series to create an invoice. I do have a quick question though …. Can I protect the sheets so certain cells can’t be changed without messing up the macros?
Great tutorial. I do have a problem with saving as Excel file. I have double checked everything but the link for save as Excel should be in column H but it comes out on the next row below all the invoice details with a red background. Any help would be appreciated.
Is there a way to have the invoice auto populate more lines for a multiple page invoice as it fills up with data? I’m experimenting with VLookup, Index&Match and MailMerge to get my information on to the invoice. I’m having trouble with having it add invoice lines and thus I’m turning back to a traditional mail merge using Microsoft excel/word combo.
All works 100%....When saving the file it removes the buttons as supposed to but for some reason it removes my logo image as well..any advise would be super helpfull...
I had the same problem. found an answer that worked for me. under: For Each shp In ActiveSheet.Shapes (add): If shp.Type msoPicture Then That's what worked for me. I hope it helps. BTW don't put (add).
@@jaimegonzalez8653 I am struggling to find where to put it, I add Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Type msoPicture Then shp.Delete Next shp but the it says you can't have a next without for, and not sure why it's throwing that error
Hi Tuwell This series of automated invoice is great. But , I am working online on Google One Drive, all my files I want to store in Google Drive , and how to get the file folder and path. Need solutions for this.
Love the series! but every time i run the macro it just opens up a new book in a tab and gives me a Run time error 9 subscript out of range. it will not save to the file location i have selected? my Macro looks the same as yours except the file location is mine?
Hi Chester I cant believe what I have achieved by just watching your tutorial's, I have been helping my partner with his small business setting up invoices etc I am a novice but I have managed to do this apart from some small hiccups along the way. one of being I cannot seem to get past the .SaveAs Filename = path & fname, FileFormat:=51any idea what I am doing wrong? TIA
Hi Chester. When I create a new invoice, the old one does not save as an excel in the folder I chose and does not post in the record of invoices. How can I go back and to fix it?
Hi Chester, again thanks for these great tutorials! Question; I work on a Mac and finished the SaveInvAsExcel tutorial and it does create an .xlsx file, but I'm getting a Run-time error '1004': Your changes could not be saved to '202208 - ' because of a sharing violation. Try saving to a different file - and then I can choose between; Debug or End - When Debug is chosen, it is the row with the FileFormat:=51 which is highlighted. Any idea how to solve this? And please don't say buy a PC 😀 The path = ... I'm using the / (forward slashes) as needed with a Mac.
Your series really helped for my small business that i just started up! Huge thanks to you for that. However, if you could help with any idea on how to copy the invoice Descriptions onto a separate worksheet (as a compilation of all descriptions of the entire invoices that we have saved), it would mean so much to me! *crossed fingers for a reply from u*
Thank you so much for these very informative videos. I am currently creating a template for 2 companies (my own and a business associate) and it has been really helpful. If I am honest I do not understand the coding very much and I have had an error come up where the macro finds an error with the path I have chosen to save the xl file. I am unsure if this is because the invoice numbers I use are numbers and letters (I have changed this from long to string) but I cannot seem to get rid of the error!
thanksful for the great series. However, I have a problem with the code line .Sheets(1) .Name = "Invoice" it shows compile error expected: End of statement and if I separate the two code lines it shows me invalid use of property. so what shall I do???
Hello Chester, Thanks for sharing this useful courses series. I am trying to follow up and practice by the way. I am facing currently a problem when I try to check if the macro works RUN-TIME ERROR '438' OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD I want to understand what's happen and how to solve the problem. I can share a screenshot if necessary... Thanks for your assistance please
How to update stock every time automatically, if we are maintaining stock in another worksheet, after saving each invoice worksheet, if there are many no. of items in a invoice. Kindly make a separate video on it.
Hi chester im new in your tutorials, let me tell you first, they are great! but i found a situation that i dont know how to fix it. after configure all the macros i found that the save as excel macro instead of saving the invoice in a new excel it behave as the new inbox macro, they are someone having this Issue? save as pdf and the other macros works perfectly but save as excel is not, i dont know what i do wrong
Okay Guys I found my answer, sometimes is good to go back and look everything until you find the error. I will let here my experience, this is what happened. The button for a weird situation had in his code pointing the new inbox macro and the icon of saving the excel was connected with save excel option on it. I just delete the button created again pointing to save as excel macro and work perfectly. Be careful with that
Hi Chester, How can I create a VBA to save attachment (Excel file) in my location drive when email received on my outlook folder and using the file name based on cell value...Many thanks
Help please! I am getting an error message when I try to run the macro and then open the excel file via the hyperlink. The error message states: "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed." Is the file format code different for Apple from the '51' you provided in your video?
This videos are so detailed. Thank you so much. But I need help. I have go through this videos and currently I am stuck on the step to create hyperlink to invoice and recording it on the "record of Invoice Tab". I have write all the step as stated on the video and when I try to test to see if the Macro works, I get an error message "1004" and highlighter in yellow is this ( .SaveAs Filename:=path & fname, FileFormat:=51 ) without the parenthesis. not sure why this is not working. Those it make a difference if I am using a Mac? everything else is working perfectly as shown on the videos. any suggestions.
Just figured it out, I had to add an extra slash / at the beginning of the path. It should look something like this: path = "/Users/folderA/folderB/folderC/"
Hi Chester, I have followed your tutorial as far as recording within the record of invoices sheet, however, when running the macro the 'nextrec' comes back with error and when hovering over 'nextrec = invno' is states nothing. There doesn't appear to be any typo's - any suggestions? Also, when the invoice is saved down into a new file it loses our company logo - any suggestions on this? Many thanks Jo
Does anyone know how to get the duplicate to save in a correct folder?....My module works and creates a new invoice but Tugwell does not show how he get to a saved folder. His page switched to where the file should be saved (12:07 of the video) but he doesn't tell us or show us that step....therefor the second half of the module will not work with the hyperlink because it does not know where to save it.....hopefully that makes sense. someone please help.
So when I hit Create New Order all the info Deletes correctly. However I dont exactly need PDF's. Is there a way to Create a Hyperlink directly on the Invoice Number that reverts the "Invoice Page" back to previous if that makes sense. Like if i have multiple open orders at once. But would like to log them simultaniously. Great Videos they have helped me a great deal. I might be trying to accomplish to much but any help. Thanks!
Hi Chester, I love your informative videos. I am a novice in excel, and I'm creating an invoice with your demonstration. I am at the save to Excel file, when I run the Macro to the save folder, the file shows the saved Invoice as 132 - B10.xls and not the customer's name. I have double checked the Macro formula and it's identical to yours. What do you think I've done wrong? Alistair
This is extremely helpful, thanks for sharing this Chester! I am a Mac user and I did find some differences at this stage. I have 2 questions if anyone can help at all. Can the files be saved automatically in Sharepoint? I linked this to my Mac and with the Mac's filepath it does save to the Sharepoint document library I want, however, not sure if this would only work for me or other users that would access template and issue invoices. The second question is actually the bigger problem, when my file is saved it doesn't actually recognise the file format ad I cannot find out why (this is what I wrote in the code, as per video: .SaveAs FileName:=path & fname, FileFormat:=51).
Hello Chester! 1st off, thank you for your wonderful videos. I have been following this series and so far so good. Excuse my ignorance, but where do I get the "path" part from? I'm stuck, you had yours on your clipboard, but where did you copy it from? Thanks in advance.
Hello Chester, thank you for your informative video. I find it is easy to follow your instructions and I manage to create the invoices as explained in your videos. I encountered with 2 issues though. 1) I am not able to group the icon and the form. When I right click, there is no 'group' button, 2) When I save the invoice to the .xlsx format, the company logo is missing. Could you assist me with these 2. Other that, all the 3 videos I watched are fantastic and easy to follow. Thank you.
hey there, thank you foor these good videos. when i save as .xlsx and i open the file my loge is gone. on every one haha. is this happening in the step: For Each shp In ActiveSheet.Shapes shp.Delete ? how can i save my logoo so that does not happen ? greatings
Hi Chester, Thanks very much for this series of videos, they are really helpful! I've run in to a problem with the Record of invoice micro, it comes up with error 424 at line nextrec.Offset(0, 1) = custname. Any ideas? Thanks
Hi Chester, I have a problem when running the VB code there is a bug on the line - .SaveAS Filename:=path & name, Fname, FileFormat:= 51. Does this relate to me using One Drive and if so what would the correct path be ? 😅
and can u help with this.... u did exact sheet copy....but i want to paste its values and formats including coloumn width....can u help me with this two?... please
I am having trouble with this formula. I have adapted it for a Method statement and not an invoice. The Method Statement is Sheet 3, called "METHOD STATEMENT", the sub routine runs down as far as With ActiveWorkbook .Sheets (3).Name = "METHOD STATEMENT" Which is highlighed in Yelllow, indicating that there is a fault that i need to overcome The whole routine is below: Sub SaveMetStaAsExcell() Dim MetStaDt As Date Dim TiOfWrTi As String Dim ConPoNo As Long Dim QtReNo As String Dim ViReNo As Long Dim MetStaNo As String Dim RiAsref As String Dim CliNm As String Dim CliAdd As String MetStaDt = Range("B3") TiOfWiTi = Range("B4") ConPoNo = Range("B6") QtReNo = Range("B8") MetStaNo = Range("B9") RiAsref = Range("B10") CliNm = Range("B11") CliAdd = Range("B17") Path = "C:\Users\???.???????\MS Files\MS 2022\" fname = MetStaNo & " - " & CliNm 'Copy in the Method Statement as a New Workbook Sheet3.Copy 'Delete all the buttons on the worksheet Dim Shp As Shape For Each Shp In ActiveSheet.Shapes Shp.Delete Next Shp 'Save the new workbook to a specified folder With ActiveWorkbook .Sheets(3).Name = "METHOD STATEMENT" .SaveAs Filename:=Path & fname, FileFormat:=51 .Close End With Close the workbook 'then need to put the details of the method statements in the record of method statements End Sub
The macro will rename a sheet in the new workbook. So instead of creating a new excel file with sheet named "Invoice Template", we want to create a sheet named "Invoice". So you need to put .Sheets(1).Name = "Your name here, for ex Invoice". Correct would be: With ActiveWorkbook .Sheets(1).Name = "METHOD STATEMENT" .SaveAs Filename:=Path & fname, FileFormat:=51 .Close End With
Can anyone explain whats going wrong on the save workbook to specified format. Where it starts with .Sheets(3).Name = "Invoice" I get an error 9 code "Subscript out of range". Have been stuck for about 2 hours, just staring at the code 2 c what i done wrong. Any ideas? Thanx
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
thanks you so much Chester for your support.
Legends you are my good man
I'm like this guy has been so great its gotta be in the comments ( AMAZING ) !! Just got rid of quick books wayyyy to costly thanks so very much from all of us !
I have found these videos to be absolutely brilliant. There seems to be an issue with excel on MAC when trying to go through the process of converting the document to a PDF - it wasn't a necessary function for me though. The only other problem is that I have to have the original document open whenever I open other excel documents, otherwise it does not recognise reference points for the customer database.
Greats videos and very educational for anyone who isn't confident with macros.
Chester, you are a ROCKSTAR for doing these videos. Wow, you have overhauled my entire invoicing system and given me a massive crash course in the wonders of Excel. Thank you!
Same!
Hi Chester, I was pretty green to Excel when I found your series and found it very useful as I was asked to create just a simple invoice form to be typed up, however I stumbled upon you video and followed your instructions modifying to their needs. I added a small form to sheet1 on the right hand side to easily add customer details inc phone, mobile and included formatting of phone and mobile numbers to make them more readable and a check for number of digits. also used PROPER to enable quicker typing in of name and addresses along with a macro to add to customers sheet2. Also after showing to the recipient there where two requests, 1. to have more space to add items on some invoices, so I added a longer form and two macros for a 'One page Invoice' and a 'Two page Invoice,' the One page Invoice macro button Hides cells in the middle part of the form and the Two page Invoice button Unhides the same cells. 2. Which I haven't found a way of doing as yet is to be able to re-open an edit (add to) an Invoice after another has been started, this is because many of there jobs are awaiting parts which often get delayed. I did think maybe adding an incremental version number to the saved filename or something, Any ideas on how this could be done?
Chester! Great Job I liked your teaching style. Thank you for that, I have one creation of PDF saving file but will also look into creating what you just teach herein Good Job Sr.
Great Chester! Thanks for sharing this detailed project. Thumbs up!!
Thanks! I am like time number 10 of watching and learning your videos
Very generous - thanks
@@ChesterTugwell hopefully it buys you whatever your drinking :)
Thank you so much for making this video it has made my life easy I just started a smell coming and your video really help me actually it’s saved my lot of time it’s very easy
If you are on a mac, and if you end up with the filetype saving as a "Document" despite declaring the filetype to be a 51 (xlsx?) type. You can replace the following line:
.SaveAs FileName:=path & fname, FileFormat:=51
with this one:
.SaveAs FileName:=path & fname & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Now all of my files save as .xlsx files and my mac opens them up with excel by default when I double click.
Hope I did it right. I'm really new to this so please correct me if I'm wrong :)
Brilliant! Thanks for sharing this. It works
I want to thank for this tutorial, it is awesome and works just fine! Now, I know very little of Excel and nothing about VBA, could someone tell me how to modify the code to save the file using an specific cell from the template?
Hi Chester,
Your videos are amazing as ever.
I seem to be having an issue with my invoice number, its always pulling through as 0? I've tried changing the formatting but to no avail? Please help
Hello, Thanks for the great video's it helps me a lot. After i do all if it what you say in the video it works perfect only when i push the hyperlink he cant open my xlsx, can you tell me what i can do with it so he will open it. Thank you
Dear Chester. Thank you for these tutorials. Very helpful. However, i have had trouble with the latest video in order to store the file as a normal excel file. I have used the fileformat:=51 code in the macro but when i play this, it saves as an unrecognised "A File"? I have double checked all the code and this is what appears to be the problem. ANy suggestions?
don't foget the space
UGH I'm having the same issue, in the Invoices folder where it's saved to the "Type" is just File????
Use this: .SaveAs FileName:=path & fname & ".xlsx"
Hi, tks so much for this tutorial. I was wondering if it's possible to use it with google sheets?
Hello Chester, It is a great series from your side. Greatly appreciate your efforts in compiling and coaching the invoice automations. Just one clarification is needed. Whenever I save the new file in the folder and open it, I found that the VBA modules are not copied. I have also mentioned in the template file VBA module as .SaveAs Filename:=Path & FName, FileFormat:=52, which should allow the file to be saved as xlsm and retain the VBA modules. I require this way because I have SpellNumber VBA working in an Invoice cell. Can you please tell me where I may be going wrong, Please ?
Hi Chester!
Thanks for your invoice series. I am new to VBA but with your help I have done a invoice generation excel with all the features you have explained and now I am getting a message while saving the invoice as normal xls and it is asking for confirmation with these option - SAVE, SAVE AND ERASE FEATURE, GO BACK, HELP - Please help to avoid this situation.
Thanks a lot.
Regards
Abhi
Im trying do the SaveinvAsExcel and I keep getting this error. I have done everything step by step as you but still getting the same error 1004
nextrec.Offset(0, 1) = custname, this is giving me a debug issue. Can you help me?
Same problem, any idea how to fix it?
This series has saved me! I cancelled by Self Employed QuickBooks subscriptions. The only issue I'm having is when I save the colors in the formatting changes to a darker color from the original. Any way to fix that?
Hello Chester, thank you for the great tutorial. I am very new to the VBA and I find it really helpful and easy to follow. I face one problem. When I try to save the file a message 1004 appears, about the use of special characters (although I don't use any). Can you please help me with this?
for the path=
for macbook what to key in
Hi Chester, great videos :) Just a quick one is there a way of changing the location of a saved file? We work in job numbers and each job has its own folder i.e J24001, J24002 etc so in the save as xlxs macro I would like to save each invoice to a separate folder, is this possible?
Thanks!
Many thanks
@@ChesterTugwell The more I see your videos and explanation, the more I am impressed. Great Learning
For path insert what did you Copy and Paste ?
Hi Chester, I felt like a superstar after being able to build the document from scratch following your videos, now I am facing an issue, I had this document saved on my computer with the paths also to my computer, Today I by accident had everything synced to Onedrive and for this reason nothing more is working. Is there any way out of this issue?
Sir, can we add two macros in single button like 'save as PDF and save as Excel file' in one button? because when i clicking these buttons they are creating hyperlinks to different next row so, make it like both these hyperlink in same row . plz
Love your videos. Thank you thank you
Have gone through all of the videos in this series to create an invoice. I do have a quick question though …. Can I protect the sheets so certain cells can’t be changed without messing up the macros?
I got stuck at SaveAs Filename:-path & fname, FileFormat:=51 it highlights it in Yellow and doesn't save when I play the macro. Any ideas?
It's SaveAs Filename:=path & fname, FileFormat:=51
You used :- instead of :=
I saved as 51 with doubt, while I Google it ..I came to know 51 and 52 we can use it
My sheet s running without fail
@@farjanabegum4113 here in the Netherlands i used 52 and it worked thanks
Use this: .SaveAs FileName:=path & fname & ".xlsx"
Great tutorial. I do have a problem with saving as Excel file. I have double checked everything but the link for save as Excel should be in column H but it comes out on the next row below all the invoice details with a red background. Any help would be appreciated.
Is there a way to have the invoice auto populate more lines for a multiple page invoice as it fills up with data? I’m experimenting with VLookup, Index&Match and MailMerge to get my information on to the invoice. I’m having trouble with having it add invoice lines and thus I’m turning back to a traditional mail merge using Microsoft excel/word combo.
All works 100%....When saving the file it removes the buttons as supposed to but for some reason it removes my logo image as well..any advise would be super helpfull...
I had the same problem. found an answer that worked for me. under:
For Each shp In ActiveSheet.Shapes (add):
If shp.Type msoPicture Then
That's what worked for me. I hope it helps. BTW don't put (add).
@@jaimegonzalez8653 I am struggling to find where to put it, I add
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type msoPicture Then
shp.Delete
Next shp
but the it says you can't have a next without for, and not sure why it's throwing that error
Try this:
For Each shp In ActiveSheet.Shapes
If shp.Type msoPicture Then shp.Delete 'This line is modified so that is doesn't delete the logo
Next shp
@@ChesterTugwell that worked 100%%% thank you so much Chester!
Hi Tuwell
This series of automated invoice is great.
But , I am working online on Google One Drive, all my files I want to store in Google Drive , and how to get the file folder and path.
Need solutions for this.
Love the series! but every time i run the macro it just opens up a new book in a tab and gives me a Run time error 9 subscript out of range. it will not save to the file location i have selected? my Macro looks the same as yours except the file location is mine?
Hi Chester I cant believe what I have achieved by just watching your tutorial's, I have been helping my partner with his small business setting up invoices etc I am a novice but I have managed to do this apart from some small hiccups along the way. one of being I cannot seem to get past the .SaveAs Filename = path & fname, FileFormat:=51any idea what I am doing wrong? TIA
can we make automatic inventory system and attached to these invoice?
Hi Chester. When I create a new invoice, the old one does not save as an excel in the folder I chose and does not post in the record of invoices. How can I go back and to fix it?
Let me rephrase. I can create a new invoice. I tried to a save as xlsx but the macro doesn't work.
Hi Chester, again thanks for these great tutorials! Question; I work on a Mac and finished the SaveInvAsExcel tutorial and it does create an .xlsx file, but I'm getting a Run-time error '1004': Your changes could not be saved to '202208 - ' because of a sharing violation. Try saving to a different file - and then I can choose between; Debug or End - When Debug is chosen, it is the row with the FileFormat:=51 which is highlighted. Any idea how to solve this? And please don't say buy a PC 😀
The path = ... I'm using the / (forward slashes) as needed with a Mac.
Use this: .SaveAs FileName:=path & fname & ".xlsx"
Your series really helped for my small business that i just started up! Huge thanks to you for that. However, if you could help with any idea on how to copy the invoice Descriptions onto a separate worksheet (as a compilation of all descriptions of the entire invoices that we have saved), it would mean so much to me! *crossed fingers for a reply from u*
I have a problem with the code line .Sheets(1) .Name = "Invoice" it shows "run time error"9":, subscript out of range", so what shall I do???
Thank you so much for these very informative videos. I am currently creating a template for 2 companies (my own and a business associate) and it has been really helpful. If I am honest I do not understand the coding very much and I have had an error come up where the macro finds an error with the path I have chosen to save the xl file. I am unsure if this is because the invoice numbers I use are numbers and letters (I have changed this from long to string) but I cannot seem to get rid of the error!
Anyone else know how to solve the runtime runntime error 1004 - on Mac
thanksful for the great series. However, I have a problem with the code line .Sheets(1) .Name = "Invoice" it shows compile error expected: End of statement and if I separate the two code lines it shows me invalid use of property. so what shall I do???
Hello Chester,
Thanks for sharing this useful courses series.
I am trying to follow up and practice by the way. I am facing currently a problem when I try to check if the macro works
RUN-TIME ERROR '438'
OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD
I want to understand what's happen and how to solve the problem.
I can share a screenshot if necessary...
Thanks for your assistance please
How to update stock every time automatically, if we are maintaining stock in another worksheet, after saving each invoice worksheet, if there are many no. of items in a invoice. Kindly make a separate video on it.
Hi chester im new in your tutorials, let me tell you first, they are great! but i found a situation that i dont know how to fix it. after configure all the macros i found that the save as excel macro instead of saving the invoice in a new excel it behave as the new inbox macro, they are someone having this Issue? save as pdf and the other macros works perfectly but save as excel is not, i dont know what i do wrong
Okay Guys I found my answer, sometimes is good to go back and look everything until you find the error. I will let here my experience, this is what happened. The button for a weird situation had in his code pointing the new inbox macro and the icon of saving the excel was connected with save excel option on it. I just delete the button created again pointing to save as excel macro and work perfectly. Be careful with that
Hi Chester, How can I create a VBA to save attachment (Excel file) in my location drive when email received on my outlook folder and using the file name based on cell value...Many thanks
Help please! I am getting an error message when I try to run the macro and then open the excel file via the hyperlink. The error message states: "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed." Is the file format code different for Apple from the '51' you provided in your video?
Use this: .SaveAs FileName:=path & fname & ".xlsx"
This videos are so detailed. Thank you so much. But I need help. I have go through this videos and currently I am stuck on the step to create hyperlink to invoice and recording it on the "record of Invoice Tab". I have write all the step as stated on the video and when I try to test to see if the Macro works, I get an error message "1004" and highlighter in yellow is this ( .SaveAs Filename:=path & fname, FileFormat:=51 ) without the parenthesis. not sure why this is not working. Those it make a difference if I am using a Mac? everything else is working perfectly as shown on the videos. any suggestions.
Same problem here
Just figured it out, I had to add an extra slash / at the beginning of the path. It should look something like this:
path = "/Users/folderA/folderB/folderC/"
Use this: .SaveAs FileName:=path & fname & ".xlsx"
Hi Chester, I have followed your tutorial as far as recording within the record of invoices sheet, however, when running the macro the 'nextrec' comes back with error and when hovering over 'nextrec = invno' is states nothing. There doesn't appear to be any typo's - any suggestions?
Also, when the invoice is saved down into a new file it loses our company logo - any suggestions on this? Many thanks Jo
Hi Chester,
I am waiting for your advice regarding this problem
I'll be greatful to you for your help
Rgs
Abhi
Does anyone know how to get the duplicate to save in a correct folder?....My module works and creates a new invoice but Tugwell does not show how he get to a saved folder. His page switched to where the file should be saved (12:07 of the video) but he doesn't tell us or show us that step....therefor the second half of the module will not work with the hyperlink because it does not know where to save it.....hopefully that makes sense. someone please help.
Mine saves to the desktop and not the folder dont know what I’m doing wrong either 😂
I can get everything to work, however it won't save "as an excel file format". Working on a Mac. Is there a different code other than 51?
So when I hit Create New Order all the info Deletes correctly. However I dont exactly need PDF's. Is there a way to Create a Hyperlink directly on the Invoice Number that reverts the "Invoice Page" back to previous if that makes sense. Like if i have multiple open orders at once. But would like to log them simultaniously. Great Videos they have helped me a great deal. I might be trying to accomplish to much but any help. Thanks!
what if invoice number repeated as new invoice by mistake? will it show thar invoice number is already taken?
Could you please help with where to find the path
How can one display only the filename as a hyperlink in the record, and not the whole file path?
Hi,
Please there is an issue oh!
It picks my logo as a shape, what should I do?😢
I would like to create the invoice I have created to all different departments, how do I make the invoice save it in a general area like downloads?
Hi Chester, I love your informative videos. I am a novice in excel, and I'm creating an invoice with your demonstration. I am at the save to Excel file, when I run the Macro to the save folder, the file shows the saved Invoice as 132 - B10.xls and not the customer's name. I have double checked the Macro formula and it's identical to yours. What do you think I've done wrong? Alistair
I’m having the same issue as you are. Did you manage to fix it?
This is extremely helpful, thanks for sharing this Chester! I am a Mac user and I did find some differences at this stage. I have 2 questions if anyone can help at all. Can the files be saved automatically in Sharepoint? I linked this to my Mac and with the Mac's filepath it does save to the Sharepoint document library I want, however, not sure if this would only work for me or other users that would access template and issue invoices. The second question is actually the bigger problem, when my file is saved it doesn't actually recognise the file format ad I cannot find out why (this is what I wrote in the code, as per video: .SaveAs FileName:=path & fname, FileFormat:=51).
Use this: .SaveAs FileName:=path & fname & ".xlsx"
Hello Chester! 1st off, thank you for your wonderful videos. I have been following this series and so far so good. Excuse my ignorance, but where do I get the "path" part from? I'm stuck, you had yours on your clipboard, but where did you copy it from? Thanks in advance.
If you go to the folder you want them to save to, right click, select Properties, and copy the Location.
@@autumnm3751 Thanks a bunch for your help, last time I used Excel was back in college 1998 😕. Im really trying here
Can please make video for the quote as well
Hello Chester, thank you for your informative video. I find it is easy to follow your instructions and I manage to create the invoices as explained in your videos. I encountered with 2 issues though. 1) I am not able to group the icon and the form. When I right click, there is no 'group' button, 2) When I save the invoice to the .xlsx format, the company logo is missing. Could you assist me with these 2. Other that, all the 3 videos I watched are fantastic and easy to follow. Thank you.
I am having the same issue...
same issue here
hey there,
thank you foor these good videos.
when i save as .xlsx and i open the file my loge is gone. on every one haha.
is this happening in the step: For Each shp In ActiveSheet.Shapes
shp.Delete
? how can i save my logoo so that does not happen ?
greatings
ok fixed my problem.
for those with the same problem: i made a header, put my logo in the header, and set the header on only first page.
Thank you so much for those videos. Can we use a Onedrive link for the path instead of a Windows path?
If you add Onedrive to your windows search yes
Did this work? I've got the exact same problem, I want my files to be saved in my shared OneDrive
Hi Chester, Thanks very much for this series of videos, they are really helpful! I've run in to a problem with the Record of invoice micro, it comes up with error 424 at line nextrec.Offset(0, 1) = custname. Any ideas? Thanks
Same thing happened to me. Any idea how to fix this?
I am facing the same problem, did you got any idea to resolve it?
Same problem, any idea hot to fix this?
Im stuck on the clear contents part
On create new invoice
Hi Chester, I have a problem when running the VB code there is a bug on the line - .SaveAS Filename:=path & name, Fname, FileFormat:= 51. Does this relate to me using One Drive and if so what would the correct path be ? 😅
hi Im having the same issue did you manage to resolve this?
thanx
hi bro...i have a problem here, i just did what yoou have done in the video, but my invoice number keep getting in to currency formet...can u help
and can u help with this....
u did exact sheet copy....but i want to paste its values and formats including coloumn width....can u help me with this two?...
please
Can i get this template?
can you help me with my invoice
Can you help please
I am having trouble with this formula. I have adapted it for a Method statement and not an invoice. The Method Statement is Sheet 3, called "METHOD STATEMENT", the sub routine runs down as far as
With ActiveWorkbook
.Sheets (3).Name = "METHOD STATEMENT" Which is highlighed in Yelllow, indicating that there is a fault that i need to overcome
The whole routine is below:
Sub SaveMetStaAsExcell()
Dim MetStaDt As Date
Dim TiOfWrTi As String
Dim ConPoNo As Long
Dim QtReNo As String
Dim ViReNo As Long
Dim MetStaNo As String
Dim RiAsref As String
Dim CliNm As String
Dim CliAdd As String
MetStaDt = Range("B3")
TiOfWiTi = Range("B4")
ConPoNo = Range("B6")
QtReNo = Range("B8")
MetStaNo = Range("B9")
RiAsref = Range("B10")
CliNm = Range("B11")
CliAdd = Range("B17")
Path = "C:\Users\???.???????\MS Files\MS 2022\"
fname = MetStaNo & " - " & CliNm
'Copy in the Method Statement as a New Workbook
Sheet3.Copy
'Delete all the buttons on the worksheet
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next Shp
'Save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(3).Name = "METHOD STATEMENT"
.SaveAs Filename:=Path & fname, FileFormat:=51
.Close
End With
Close the workbook
'then need to put the details of the method statements in the record of method statements
End Sub
Its same with me how you can fix it? Pls
The macro will rename a sheet in the new workbook. So instead of creating a new excel file with sheet named "Invoice Template", we want to create a sheet named "Invoice". So you need to put .Sheets(1).Name = "Your name here, for ex Invoice".
Correct would be:
With ActiveWorkbook
.Sheets(1).Name = "METHOD STATEMENT"
.SaveAs Filename:=Path & fname, FileFormat:=51
.Close
End With
Can anyone explain whats going wrong on the save workbook to specified format. Where it starts with .Sheets(3).Name = "Invoice" I get an error 9 code "Subscript out of range". Have been stuck for about 2 hours, just staring at the code 2 c what i done wrong. Any ideas? Thanx
Same problem here... Any solutions?