Save Invoice Worksheet to New Workbook | Filename Based on Cell Value | Create Hyperlink to Invoice
Вставка
- Опубліковано 5 лип 2024
- Download the featured invoice template here
www.bluepecantraining.com/exc...
****UPDATE: I've modified the code that deletes all the shapes on the worksheet, so that it doesn't delete your logo. Download the template or see my reply to the pinned comment to get the modified version of the code. Thanks to Ron Bemis for pointing this out. ****
Full playlist for this video series: • Create an Automated In...
Check out Simple Sheets Invoice Template (all purchases help support this channel)
www.simplesheets.co/a/2147525...
This tutorial is part of a series of tutorials that demonstrate how to create an automated invoice system in Microsoft Excel. This video looks at how to write a VBA macro that saves the invoice worksheet to a new workbook and then automatically records it on the invoice tracker.
Other videos in this series show you how to write VBA macros that
1) Automatically generate the next invoice number
2) Add the invoice details to a record of invoices
3) Save the invoice as a PDF
4) Automatically email a PDF version of the invoice to the customer
Table of Contents:
00:00 - Introduction
03:00 - Start writing the code for the VBA macro
05:14 - Declaring variables for the macro
08:26 - VBA to copy specific worksheet to new workbook and save
09:07 - VBA to delete all shapes on a worksheet
09:48 - VBA to save a workbook to specified folder & use cell value as filename
12:44 - VBA to find next blank row
14:20 - Copy invoice details to invoice tracker
15:41 - VBA to create a hyperlink to link to another Excel workbook
17:22 - Create a button to run the macro
------------------------ - Навчання та стиль
Great Chester! Thanks for sharing this detailed project. Thumbs up!!
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!
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 !
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?
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.
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
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.
Thanks! I am like time number 10 of watching and learning your videos
Very generous - thanks
@@ChesterTugwell hopefully it buys you whatever your drinking :)
Love your videos. Thank you thank you
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
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!
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.
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?
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???
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
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 ?
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 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.
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?
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, tks so much for this tutorial. I was wondering if it's possible to use it with google sheets?
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!
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?
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
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.
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 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 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?
Thanks!
Many thanks
@@ChesterTugwell The more I see your videos and explanation, the more I am impressed. Great Learning
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"
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! 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
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?
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?
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?
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.
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
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
thanx
For path insert what did you Copy and Paste ?
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"
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
Same problem here
can we make automatic inventory system and attached to these invoice?
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
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"
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???
Hi,
Please there is an issue oh!
It picks my logo as a shape, what should I do?😢
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 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
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!
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.
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
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"
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?
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?
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
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"
nextrec.Offset(0, 1) = custname, this is giving me a debug issue. Can you help me?
Same problem, any idea how to fix it?
Can i get this template?
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 😂
Anyone else know how to solve the runtime runntime error 1004 - on Mac
Hi Chester,
I am waiting for your advice regarding this problem
I'll be greatful to you for your help
Rgs
Abhi
can you help me with my invoice
Can you help please
Im stuck on the clear contents part
On create new invoice
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