Streamline Your Invoice Management Excel for Accounting

Invoice management excel for Accounting

in this video I will show you how you can automate your invoicing system in Excel you can save this invoice as a PDF and a workbook with just a single click button your PDF has been saved you can record a summary of your invoice on a separate sheet invoice recorded you can see it it is saved here with a link to your PDF invoice file you can also edit your previous invoices if you miss something enter the invoice number first we have an invoice sheet here we need to convert this into a PDF with a custom file name and custom file location for that go to your VBA code insert a new module enter your code here you can find all your codes in the link in description this code is very simple to read each and every line has been narrated I will show you only the important parts here first you will specify the cell of invoice number here which is G4 in this case G4 then you will specify the customer name cell which is B3 B3 the next line constructs the file name of your PDF which would be invoice number Dash and customer name next is the file location let me show you how you can enter this suppose this is a folder where you want to save your invoices in PDF right click go to properties copy this location enter it here in the inverted commas add a back slash now enter the last name of the folder which is invoice record after that enter a back slash now your file path has been constructed let's try it out you will insert a new button here save as PDF let me rename it you can go to the print settings to adjust your sheet this will also apply to the PDF let's try out dialog box will appear that your file has been saved successfully let's check it out here you can see it is save next is you want to save a summary of this invoice on a separate sheet I have created one here here you can get the invoice number the date customer and everything else here you will find the link to your PDF file which we will enter later for that go to your VB code insert a new module enter your code I'll show you only the important parts here first in this line here you will enter the name of the sheet where you want to save the history which is invoice history in my case next is in this line you will write the name of the table where you want to save the uh summary of the invoice which is I have named it as history you can see see this here this is history next is you will again specify the invoice number the invoice dat like we did before invoice number is in G4 G4 and all the rest of this you have customer name the amount and the due date now if you go down here here you will specify which cells to fill in the summary sheet for example the cell number one which is invoice number we will say it is equal to invoice number then cell two which is date equal to invoice date these we have specified already above here similarly in the range six we have the link to the PDF file now let's construct the path to that PDF file here you will enter the file address where your PDF file has been saved like we did before the same address will be here too so this line will add hyperlink to that PDF now let's close it we insert a new button let's try it out dialog box will appear that your invoice has been recorded let's check it out here you can see you have your invoice number the date and everything else saved here you have the hyperlink to your PDF file this will open the PDF invoice next is you want to edit some of your previous invoice for that we will also have to save our invoice as a separate Excel workbook so let's enter enter a new code enter your code here so for Excel invoices I'm creating here a new folder sort of a d folder for Excel invoices we can say Excel record here we will save all our Excel invoices we have the code here here you will specify the invoice number here you will specify the path where you want to save your invoices in Excel form like we did before you can copy the address here one more thing the Excel invoice will be saved with the name invoice number only for example 1 2 3 4 5 only for keeping things simple so let's write down you can see the invoice has been recorded as a separate Excel workbook next is we want to let's say we want to edit our invoice number 1 2 3 4 5 so we will have to open it here and edit everything so for that we'll add a new code we have a code here let me explain only the important things here so here you will enter the file path where your invoices are saved in Excel form so we have the folder Excel record here then if you go down here what this code will do is open that invoice and copy it first sheet to our existing sheet here so you can edit anything let's trite out and a log box will appear it will ask you to enter your invoice number we have our invoice number as 1 2 3 4 5 okay so our invoice has been opened here you can see our invoice 1 2 3 4 5 in our existing sheet now you can edit it as you want for example let's say this was 1,000 which you incorrectly wrote as 100 so let's edited now we will save it as a PDF again and this will override the previous PDF file you can see our invoice has been updated next we will record the invoice summary here and I log box will appear that your invoice number already exist do you want to override this data so yes voice recorded let's see when invoice has been updated now let's make a new invoice let's save our invoice as PDF first invoice saved as PDF let it out here you can see invoice has been recorded let's record our invoice summary invoice has been recorded here you can see it you can also save it as a separate workbook so now instead of having two separate buttons for saving it as PDF saving it as workbook we can have only one so go to our code mod so what this macro will do is call the first macro that that is save as PDF so we have it as called save as PDF then it will activate the second macro as well which is save sheet as workbook this is the macro name so let's close it let insert a new button let's delete our all buttons so let's create a new invoice and the single button will save your invoice as a PDF and a workbook as well this is our invoice and this is a Excel file hope you like the video