Create Your Auto Invoice Template for it Effortlessly

Auto invoice template for IT

welcome to my Channel today we will see how to prepare an automated invoice system using XR VBA first we will create the invoice template now we will rename this first worksheet name as invoice template your company name the company name in the B column B1 cell then in the H1 cell we will write invoice match the H1 and i1 cell format printer will apply the format printer here underline invoice number PO number delete and terms in the here we can match this cell from here merge and across because we will copy and paste next three cells for these cells will make this board and phone color white we'll make some border from the more borders I'll select this one for the outline and go this one for the inside lines okay invoice 2 we will select six cells under the invoice two then we will select this range from B9 to b14 it will run out to rain now we can put our logo company logo key here description quantity price what and then then finally total excluding vector select this and format to enter we'll apply here also to this cells it will take 15 rows under this topics so from here until 31 you will select from B16 to IE 31 from the move border seven this one automatic as black color this outline then this dot line for this one based online for this one right this cell b33 the customer message you will take four rows for the customer message outside border promo borders this one here and this one go the another three lines right and here we will select these three cells g33 to i33 field black color then after this here in the g34 subtotal and then wet and finally total okay right here the subtotal is going to be equal using some function the total of this excluding weight the bet is going to be using some function again the total of this vat then finally the total will be sum function the subtotal plus weight for this cell will give some border single line here double line here we will give the currency format here also to these cells as well here apply the equation for the vat let's take 20 percentage fat for each product using IF function if the quantity if we fill the description and the quantity here the quantity is not equal empty it means if there is quantity field then the vat will be the price into let's take 20 percentage or else empty same like let's take the total excluding width if the quantity is filled let's take using a function quantity not equal empty then quantity into price that is really the total excluding bit price or else empty let's apply to the old rows now here after the customer message we will put our company details also in the g39 cell payment details we'll select this cell and double click in the format finder and we will apply this to here the company details here will be company number number telephone number and email then in the frame and details account number Bank record and the details we prepare the invoice template here fight everything done here you can assign your company logo now we will select this range okay and from the pH layout we can set this to print area in the print area set print area right now you can set this to print preview you can see set the page layout of this invoice template printable in one page Ctrl P you can set this it should be in one page now the invoice template is ready we will move to the VBA coding the first one is we'll take an another sheet and we will rename it as customers here we will record the custom details and this is the few customer details here we added and in the invoice template this is a table now we will name this table as custom list okay in the design this customer list then we will select this custom name there are two customer details there then we will select these two custom name and in the name box you will name this as customer name customer underscore name twice will enter when you select this customer name it will be selected now in the B9 cell we will take a rope down from data menu the data validation list The Source will be equal customer underscore name you can see this here custom names are updated here when you update new custom names here let's say Peak or limiter you can see in the drop down the new customer names are updated here okay here we will use some vlookup function using vlookup the local value will be this B9 cell which is the custom name from The Rock down we will fix that cell comma the table array will be from the custom seat select this table that then the column index number will be if we select the custom name ABC limited then address one will be in the B10 cell then 1 comma zero close friends you'll enter okay you will select one of the customer detail here ABC Limited then from here we have to put two that is from 2 right this is the first row this is the second row same as we will copy this formula we'll paste it here then this will be three okay paste this here this will be 4. also here then when we select the custom name only address will be captured automatically here we'll set this to left we'll put the invoice number let's say 2001 the invoice number is in the cell C3 not in the D3 p o number P O let's say Thousand and One date we will use 2D function for the date terms we will give a drop down foreign [Music] 60 days those will be the terms you can see them from the drop down you can select them let's select 30 at the moment we'll select these things and we will set them to right side now we will select one more seat here the third seat we'll name this as record of invoice here the first topic will be invoice number company amount get issued due date then the feed details now we will go to the editor window alt f11 project named as book one so from here we will insert a module right click and insert the module just double click it we will name this module as M OD sales invoice right we will enter here our first procedure is going to be sub procedure record of invoice now we will save this Excel workbook as macro enabled workbook file menu so as you serve this in the desktop we will name this file automated invoice system right the file type will be macro enable quite we will move to the editor window here in the sub procedure record of invoice here we will declare some variables okay we will declare these variables in path click before the sub procedure as per click variables public you can see in the template the invoice number custom name terms bit and the total price so invoice number as long as thing and then you can see them in the record of invoice as well then the amount pop click amount as currency public variable what's the next one date issue date issue as date then the public variable terms as byte right they're all public variables decline you can use this public variable for the old procedures in this module now we will assign the range for this each public variables in which cells these variables are there the invoice number is in the cell C3 you can see in the name of cell C3 so invoice number is equal to brain C3 right then the custom name that is in the cell B9 you can see custom name is equal to range B9 then amount amount is equal to range here you can see it is in the cell the total I 36 you can see in the name box i36 right the next one is date issued is equal to range you can see that if you here in the cell C5 okay terms in the cell C6 foreign C6 we assign these variables now here up to the sub procedure line we will declare a variable in this procedure so dim next record as range right now we will set this variable set next record is equal this next record is going to be here let's say this is the first record the next record will be here in the worksheet record of invoice we'll copy this see name in the active workbook Dot the worksheets in which you exit the copied works is the record of invoice in this worksheet from the a column range A1 Dot end open friend Excel up so this is the last row of this a column that is you can see here the last is 10 48 576 so here 10 48 576 from the last row then Excel is going up then one row down using offset only one row down right this one we are sending to next record so you can see from the active workbook worksheet of this record of invoice the range is from this range and Excel is going up then one row down so invoice number will be in the a column so we will assign the inverse number to the next y code next record invoice number also we will assign the custom name to the next record then offset one column next record dot offset only one column so no changes in row so comma space one close friend this is assigned by this the one that is company name right then the next record offset open friend comma Space 2 this is assigned by amounts next record offset open Trend comma Space 3 close plan this is assigned by date issue then again next record offset open pen comma Space 4 cross fan equal this one is assigned by which one the due date how we are going to get the due date D date will be date issue plus terms date issue class terms now here we will save the active workbook after recording the three days active workbook dot so now we will compile this VBA project okay no issue we will serve this now we will jump to the Excel file in the invoice template from the insert menu we'll take SCA here okay like this the same color Tableau color save outline no outline the shape of X will give a very well shape White right click and edit test this is for the record of invoice now we will right click this save assign macro we will assign the record of invoice procedure right now the macro is ascended now we will click here let's check the third seat record of invest the details are recorded here the invoice number company name amount the tissue DD right now we will go back to English template again now we will record this again okay now we'll check the record of invoice it is recorded this guy we don't want to record the same invoice details again and again so I will delete this right core only one record is here what we are going to do here I will declare two more variables dim a comma B as range now here I will set them set a is equal to this we will copy this one right and this is the a then we'll set B we will be from inverse F template we'll copy that paste it here the invoice number whereas the invoice number here in the cell C3 so cell C3 this one right now you can see let's say A and B variable are assigned by it is now here we will put an IF function if a not equal B then we'll select this and press Tab Key that is enter shift tab ndf now it will be correct now we will check this already the details here we will remove this one as well now there is no details now we will run this procedure record of invoice okay you can see the record of invoice it is added now we will click it again it's not added again because the invoice number is already there that's why we use this if function so it is not added again so the first procedure is successfully completed the next one is we are going to save this invoice template after the first invoice completed as Excel file so our next procedure is sub procedure save as Excel now we will declare two variables by location comma file as string you can see invince numbers in the cell C3 and the custom name in the cell B9 so invoice number is in the range C3 okay also you can copy from here invest it here because we didn't declare these variables inverse number and custom name here because we declare this variable in properly so we don't need to declare here again to serve this as Excel file we need to copy this Excel see the English of template so we will copy this sheet name here worksheets paste that worksheets of invoice template dot copy we are copying that that's it after copying that there will be this saves that the macro recording saves we have to remove these shapes before saving this as Excel workbook so we will declare a variable dim save as save enter a couple of times and tab we will use a post statement here for each shape pin active sheet dot saves enter tap okay save dot delete enter sift tab two times then here next set enter same tap right now we deleted each shape from this cockpit this works it invoice now we will assign to this decline variable file location this is the location I created a called invoice in my desktop so this is the file location for the file variable we will assign what will be the file name file name will be invoice number we'll copy this invoice number and space within the word course then there will be underscore without cost close then space then we'll copy this customer name space custom name enter okay this will be in concatenate and percent here also will be n percent right sorry then file name will be saved as inverse number underscore custom name in this location now we have to rename the seat name of the copied workbook so from the active workbook the seed one we are naming the seat one dot name ask invoice right so we are naming the seed one name as invoice active workbook right also we are saving this active workbook dot save as because this is not saved yet the copied exit workbook saved as we are saving this as this base the file name colon equal you can see the file name okay and the file format the others are optional also all of these are optional we will put the file name and the file format file name will be the file location this is the variable we declared and assigned by this cut then then percent then the file comma then we will save this as which type of file the file format will be dot xlss for the DOT xlsx the file format code is 51. now you can see we declare this variable file location and file the invoice number and custom name are in this cell this variable has seen in the public so that we are copying the worksheet inversion template to save this as Excel then we are deleting the shapes from this worksheet file location and file name then we are naming the sheet name as invoice then we are saving this book in this location as dot Excel sh the code is 51. right now we will move to the workbook we'll copy this save we will name this as save as Excel right click and we will assign the macro to this save as Excel right now we will click this okay whatever okay to workbook Dot sees right now we will reset save this and we will run this again quite now we will close this and we will check this in the appropriate holder which is in the desktop you can see this is in the folder the Excel file you can see this is that's it renamed as invoice and the file name as the invoice number underscore customer name right cross and the answer our third procedure is save this invoice as video after the sales we have to save this as PDF here procedure save as finger see what we are going to use as easy we'll copy this declared variable and the invest number customer name in the which cells we copy this and we'll paste it here and also the declared variable assigned by this location and if I name we will copy and paste it here simple now end a couple of times the which range we are going to save as PDF from cell A1 to cell this is the cell i43 you can see in the name box from A1 to i43 cell we are going to save as PDF so in active worksheet dot the range is from A1 colon I-43 and that is working from this range Dot export as fixed format any space what's the type we are going to export type colon equal the Excel type PDF comma space then the file name you have to go to the file Name colon equals what is the file name finally file location and file so file location [Music] Space by also then percent then within the double course you have to save this as dot PDF cross the down course okay here export as fixed format no spaces right now it is correct now we'll compare this active active seat why it's active seat right now we'll combine this and service okay now we will move to the Excel we'll copy this save and we will rename this as edit text save as PDF right now we'll assign macro to this save as PDF now we will check this click this save okay now you'll check this in the folder the folder it is in this folder right but what we want is we'll delete this we need this in the folder name slash PDF files right this is the folder name you can see here PDF files also we can copy this will paste it here plate and it is in this folder we'll check it again right in this folder in this PDF icon it is there rename by invoice number underscore Supply name right alt F loud now we will go to the another procedure get the new invoice after saving this records and invoices new invoice we are going to change the invoice number so the invoice number is in the range C3 in the invoice template so that is equal to range C3 the variable inverse number already in the pathway as per click variable now when we run this procedure the new invest number should be increased by one so the new invoice number will be invoice number Plus 1. so range C3 is going to invoice number plus one [Music] then we are going to clear the details but we are going to act in the next invoice so we are going to remove the cells range then double quotes which one we are going to remove the PO number the date will be automatically producing today function we need to remove that um number then terms then the cell C4 C6 C4 comma space C6 comma then what is the another one only we need to remove the supplier name then another things will be automatically updated then we are going to remove the cells B9 B9 comma then the other details from the cell B17 to from the price column G 31 so from B17 to g31 B17 colon G 31 close the double quotes and close Prime Dot clear sentence we are going to clear contents only quite after that we will save this active workbook active workbook Dot so right compile answer the Excel file here we will copy this see in this as new invoice we will right click and assign this macro new invoice right we'll select this save from the format menu align left and distribute vertical right now the macro is assigned for the new invoice okay we will run this procedure new invoice there's debug around China we can't do that to a merged cell so where is the issue so okay C4 and C6 are merged cell right so we will reset and get serve this we'll come back here the C4 and C6 those are merged cells will unmerge them input left side also this one left side also this one left side now it is correct now we save this now we will run the procedure new invoice now it is correct you can see the next invoice is created here other things are clear pure number theorems these details are clear okay we will put if error before this is for all now you'll say if your number 106. terms will select from the drop down 45 here we will select a supply name ABC limited okay here we can put some description quantity or Price 45 right now we will say you will run the procedure new invest again okay you can see all contents clear then next new invoice created from 2006 to 2007. but now finally we will do one more thing in the editor window here here one more sub procedure called next customer here we will call the course sub procedures here which is by code of invoice we'll call this for sub procedure here press Tab Key okay record of invoice the next one is save as Excel the next sub procedure and the sub procedures safe as PDF another one finally the new invoice okay now we call all sub procedures under the procedure next customer now we will compare this answer move to the Excel right we will copy this save here you can resize this and change the color filter foreign this is next customer right now we will assign the macro to this next customer which is next customer now we don't need to click one by one actually we need to record the all invoice of the each invoices in the record invoice which will be recorded here one by one for each invoice number we have to save this as Excel and also as PDF this will be in different folders also after that in order this is in the order you can see first it is recording the invoice after that only the serving S6 Excel then it is serving as PDF finally after these three procedures only it is running the new invoice procedure then finally it will run the new invoice it means all contents will be deleted the new next invoice will be created here so instead of this buttons we don't need these buttons we can click this next customer button only then it is enough to do also procedure at once okay we will do it one more time remove this records and from this file we will remove this Excel file from this video file we will remove this PDF file before running this next customer proceed here the calling procedure in the save as Excel procedure we will type after running procedure the new Excel template this serving it is in the open mode so we have to close that so active workbook dot Plus instead of it we will do here in the with statement active workbook then we will delete this twice we'll Select Three N plus Tab Key after this enter shift tab then width now it is fine right you can see because I am closing this new workbook which we are serving as Excel okay now we can move to the final next procedure now we can call this in order record of invoice pass Excel then save as PDF then finally new invoice okay now we will go to the Excel file now we can call the next customer see there is hundred and thousand and one is the English number no records here no files here in the PDF files no files Excel files here I deleted everything now I will run this thousand one is the invoice number now the next invoice number is created thousand and two you can see the record of invoice that record is recorded here on the file the exercise is here the invasive saved as Excel file also the invoice is saved as PDF as well ing to need we can send the PDF invoice by email to a customers as well thank you for watching