Save Invoice Worksheet to New Workbook | Filename Based on Cell Value | Create Hyperlink to Invoice

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?

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?

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 :)

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 ? 😅

Coming from python I have been following along and using chatgpt for further explanations on some of the lines of code provided in your videos. Thanks for these tutorials!! You have a gift for teaching. Once this invoicing system is complete I'll be saving myself a lot of time as a sole proprietor.

Where else can we support you in return? Cheers

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.

Could you please help with where to find the path

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 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.

thanx

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?

Hi,

Please there is an issue oh!

It picks my logo as a shape, what should I do?😢

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?

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
the following code doesn't appear to be working on my Mac. When I try to click on the hyperlink I get a warning sign that the file is not formatted the same and when I go into the folder to open the save file its not populating as .xlsx.
SaveAs FileName:=path & fname, FileFormat:=51

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!

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

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.

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.

Can i get this template?

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

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?

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

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

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

what if invoice number repeated as new invoice by mistake? will it show thar invoice number is already taken?

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).

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

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?

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.

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 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

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

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...

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

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?

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?

Can please make video for the quote as well

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

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

How can one display only the filename as a hyperlink in the record, and not the whole file path?

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!

can you help me with my invoice

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.

Thank you so much for those videos. Can we use a Onedrive link for the path instead of a Windows path?

Can you help please

Great Chester! Thanks for sharing this detailed project. Thumbs up!!