Collaborate on Bulk Invoice Generator from Excel for Insurance Industry with Ease Using airSlate SignNow

Bulk invoice generator from excel for Insurance Industry

have you ever wanted to create your own receipt generator in Excel but didn't know how hi this is Randy with Excel for Freelancers and this week we're going to be taking a completely blank workbook and designing a full receipt generator complete with search we'll be able to add new items we're going to be able to delete print generate PDFs and email receipts it's going to be an incredible training every single step from a new workbook to a completed application is going to inside this video I cannot wait so let's get started all right thank you so much for joining me I've got a really fantastic training today in this week's training we're going to be able to take this incredible receipt generator and create it from a blank workbook I'm going to be starting absolutely from scratch we're going to be able to save our receipts we're going to be able to search for our receipts we will be able to print them out to our default printer we are also going to be able to email those and generate a PDF all inside this training and of course we're going to take you every step of the way so it's going to be a fantastic training it's a training you just won't want to miss because I'll be showing you every line of code every feature every function everything we're going to even generate this logo using AI so it's going to be a really fun training if you do like these trainings I create these comprehensive application developments each and every Tuesday for you this template this workbook is absolutely free if you just want to use it go ahead and follow the training and I'm going to show you exactly how you can use it for your own in fact you can download it for free just go ahead look for the description where download and put your name and email and we're going to get that sent right over to you I do appreciate your continued following on these trainings and I want you to get the most out of these trainings so if there's anything I can do comment below don't forget to Subs subscribe and also click the notification icon Bell I've got incredible courses and products for you a few of them will be going over but do check the links below see if you can support this channel I'll be creating an update to this application on our patreon platform each and every week I create updates based on your ideas your suggestions and your feedback so do make sure you get on our patreon because every week I create an updated template and an updated training for you all right so let's get started this is what we're going to do I'll take you just a brief overview of what we're going to be recreating from scratch we've got a receipt screen we can email the receipt simply on the click of a button it's going to attach a PDF for you so it's going to open up attach that PDF and also we're going to be able to create the PDF which you just saw print the receipt delete it save and add a new one so it's going to be relatively simple and we have a receipt database here that'll store our receipts we have an item database this is where our items of our receipts are stored we have customer list and we have an item database so that's exactly what we're going to be creating what I'm going to do like I promised you I'm going to create an absolute new workbook this is where we're going to recreate this right in front I'm just going to move it to my other screen here I'm going to keep that on my side there and we're going to be doing that the first thing what we want to do inside this brand new worksheet is we are going to save it we want to save it as and I'm just going to give it a name here and I'm going to put it in my folder which is where my free trainings are if you do like these free trainings of course I've got 350 of them available we're going to just call this receipt gener give it a different name and we're going to make sure of course this is an xlm I've got all 350 there so this is what we're going to save with receipt generator let's put the right name on that and you're going to be able to automatically create brand new receipts very very quickly and we'll get that eye in there don't want to forget that all right so let's go ahead and save this that's the first thing we want to do I'm going to give it some sheet names the first thing is the receipt so this is where we're going to create our receipts I need a receipt database so that's the sheet in which we are going to actually save the main information so we're going to receipt have issues with spelling here so we'll call this receipt database and next up what I would like to do is create a receipt item database so this is where the individual items are going to be stored and that's going to be our receipt item database next up I also would like to have a customer list so we're going to put in the customer list here and also we want items individual items so we're going to create this so the first thing what I want to do is I want to create a list of items now you and me not neither one of us want to be able to sit and watch me create a list of items but we can do that very very quickly with my AI tool pack on include the links down below this AI tool pack has six very very cool features one of which is to be able to generate data so what I want to do is I want to do that let's do something like create a list of computer items let's do 10 create a list of 10 we'll do 10 and what we would like to do is I want to include the item name description and a price so that's all I need to do then when I just select on that cell and I click get table data and it's going to get the table data it'll say processing and I will not edit this out so you can see how long it does take keep in mind that this AI assistant has six different features including fix my formula fix my code there we go so now we've got our list that's pretty cool so what I'm going to do is I'm just going to drag this over here and we're going to put that starting in row four I'm going to take the names in this case I will just use something very simple here I will not have an item ID so I'm going to double click this and auto expand it we're just going to call this item database very good so that's it pretty easy to do that we'll do the same for customers so you can see it again I'm going to merge and center this and what we'll do is we'll just increase the font to 12 put it to bold and uh I want to Center everything so we'll do that and then I'll create borders all the way around and uh we'll go ahead and format this just give it a standard blue color whatever not the font but the background that's what I want so it's sufficient for our purposes saving our work so far why don't we while we're in this sheet let's create a named range for the items so we going to go into the formulas and what I'm going do is I'm going to go into the name manager so notice there's nothing here we'll just call this item names and then what we want to do is we want a dynamic so as we add some more it's going to automatically expand we're going to offset it we're going to use that item name I'm using the header Row in case there's no data but I need to go offset one row down and no columns over so we're going to use two columns we're going to count a we need to know the number number of rows again I'm going to use that header row in a large row all the way let's see 999 but I want to exclude the header row so I'm going to use minus one I want a single column so I'm going to use one I'm going to use the Tab Key I'm going to Tab out here to make sure that I have things so that looks just fine as we see the dancing ant around that so everything looks good with that formula I'm going to click okay so we have item names as our first list I also want to do a customer so let's go ahead and close that out we're going to go into the customers we're going to do the same exact thing create a list of customers let's do 10 Again create a list of 10 customers and then what I want to do is I would like to have our customers which include customer name let's do address email and phone number very good so once again I'm going to then click on here on my AI tool pack getting the table data we I can also write code with this we can fix my code you can just simply paste in your code and it'll fix it automatically or you can write new code which which is pretty cool says it's processing now as it moves to create that data for you and there's our data that's all I really need which I'm good to go and we're going to bring that right here in row four and the headers are going to go in row three again no customer ID once again all caps customers that's our customer list that's fine enough and what I'm going to do is just merge and center that so we're going to merge and center that and then we're going to bring a font and bold that here just like we have going to Center everything making sure it's all centered giving it the background of the borders and then we're going to highlight The Columns and double click it's going to autof filter we're good we don't need this anymore okay so that's how easily we create data so it's looking pretty good once again let's do just what we did before with the items inside the formulas and let create a dynamic named range so this one we're going to do customer names again using the offset equals offset we're going to use on that header Row one row down and then no columns count a we're going to highlight a lot of row rows here 999 and then subtract one excluding the header a single column tabbing out tabing in we see the dancing ant so we've got a customer names very good so that looks good receipt item database we have a receipt item so we're going to need to know inside this database these are the items stored for our receipts so we're going to do the receipt ID I need to know that I need to know the item name I need to know the item description this is going to store the information here and I also need to know the quantity how many quantity was on that receipt our price so we're going to use the unit price on that our total I also need to know the row on our receipt so we're going to call this receipt row that means what row on our receipt is it placed and we're going to call this the database row that's sufficient so we're going to call this our receipt item database receipt item database very good often that I create these trainings this is already creative for you but this is a relatively easy training so I figured I'd do it all for you while you watch so we can see exactly how I do that and especially creating that data that can really be a timesaver and we're going to put in this Center here and then we'll bold everything and give it that blue color that's sufficient here we'll highlight The Columns and we'll double click a little bit more room for the item but that's okay so we have our receipt ID our item name our description quantity unit price total row and database row our receipt database this stores the main information we need a receipt ID Also let's put in the receipt date and also I would like to know the total keep that relatively simple I think that's going to be enough this is just our receipt database once again we're are simply going to merge and center this here just like we did before and we'll do also the same thing here all right so things are looking relatively good now for this one I would like to have a named range here as well that's going to be important for us so I'm just going to put in some sample data and I'm going to remove it but it helps to make sure that we got the named range correct back into the formulas name manager and new we're going to call this receipt ID so receip ID again once again we always use the offset because we want it to grow as our data grows we're always going to use the header because there's no issues when there's no data one row down two comms count a and then a large row that encompasses all the necessary data once we have that we are going to just Tab out and tab in to make sure that our data is correct which it is it's called receipt ID clicking okay and clicking close so those are our named ranges and then we're just going to delete that sample data into our receipt we are going to create it in this type there's no hidden columns but I'm going to use column A as a spacer it means there's nothing going to be going in that so I'm going to use a large row here and I want to create an address so the height will be a little bit high on that one and B will'll just create that we're going to need one for our items if you're not sure uh I need to make sure that it's wide enough for our items so if I take a look at our items here and I put them down here let's see inside the receipt ever just to paste them temporarily we want to make sure that we double click so the column has to be big enough for all the items that's going to be sufficient for our purposes what I'd like to do is I would like to insert shape we need to put the company address we're going to use a text box to do just that and I'm going to put it right around here and we're just going to call this frers fixes Fred's got to come in here and he's the B one fixing it and I'm going to call this 1 2 3 4 five frers Boulevard this is our company address so we want to make sure of course it's fully editable and then we're going to just put any town and then we'll put in California and then 90232 very good so now we have our address and company information let's expand that I don't really want to expand it higher but I would like to reduce the font a little bit here I'm going to use control one and then what I'm going to do is I'm going to go into the text options here we don't need any margins on this so I'm just going to zero out all the margins and then make this a little bigger so we can see everything I'm going to use control one one more time I want the fill here no fill and then we'll do no line on this shape I do want to format it using our theme color here so we'll go ahead and just set it to a blue color this one's fine I'm going to italicize it here and this F's fine I had another one originally which kind of liked so it was almost this that's good let's take a look at that one and then Bolding it so that looks kind of nice so I've got that but I would certainly also like to add a logo onto that let's create a logo while I have you here and so you can see how I create these AI generated logos so what I'm going to do is I'm going to go into my mid Journey account and I want to create a logo so let's say this is a computer shop logo let's do computer fixing so logo and I would like it blue color blue color and I want it white or transparent background white or transparent background and I'm going to check the settings I want it Square so I'm just going to make sure that it's a square and everything else is fine here so I'm just going to hit enter and it's going to start working on that and we'll come back in just a moment on that one so while our logo is generating I'm going to duplicate this here contrl D to duplicate it and I want something for our receipt so our receipt is going to be just basically in capital letters receip here and this is obviously going to have to be a lot bigger here we will put in the middle and right justify that and then put it about 20 and I don't need this italicized I don't necessarily want to use that font here uh this one's good so I like that so that's kind of basically what we have here now inside let's say about B3 I'm going to put the customer so the customer name is actually going to go in C3 so customer and then I want that customer name in here if we remember correctly we've created that data validation the list for our customers so if we're not sure we don't remember the name we can use F3 and it shows it right here customer names that's what I'd like to have so when I select on a customer name it's going to show we want to make sure it's big enough here so this was going to be our description very good so here what I want to do is when I selected the address that appear here and I want the email to appear here I want to skip a row and then inside let's say seven here I want to put the information so this is going to be our item name we can use description and I want to put the unit so this is going to be a small I just put quantity here then what I want to do is unit price here and then I want to put the total here so that's essentially going to be it but I want to format that so what I'm going to do is I'm going to highlight that I'm going to use contrl B it's going to bold it uh this is going to be our theme color this blue right here and then I'm going to change the font to White and we're going to put those in centered I'm going to use control one here the Border here I'm going to use white on that so I'm going to select on white I'm going to use this solid line right here and I'm going to use the outside and inside borders you can't see it cuz it's white but it's there so let's expand this the description is going to have to be a lot bigger total and unit price will probably be a little bit bigger so we're going to expand those quantity can be a little bit smaller so our customer here actually let's put our customer in three I'm just going to drag that up there I will see it there that way I have the address and we have the email and then we have a spacer which looks good so also inside here I want to know the receipt number so receipt number is going to go here and then our date our receipt date we'll just put a receipt date here we're going to highlight these and this one and I'm going to write justify all of these these and this I'm going to left justify here okay VBA is going to take care of the receipt number the date is also going to be so we don't need to put much there the customer BBA is going to take care of that now what we want to do is we need to know how far we're going to go down I don't want to go beyond the visible line here so I think 37 will go down so this is going to be last row of our invoice so what I'm going to do is I'm going to go all the way up there and I'm going to control one here and I'm going to put a border all the way around that so I'm just going to use a black thick border all the way around and click okay so that's going to be it obviously our description can be bigger we have enough space here we're good to go on that let's check on our logo while we're at it and those are nice I like this one here so let's go with this one here I'm going to select that one and I'm just going to download it here and okay so I've got it downloaded and now let's go ahead and insert that so so we're going to insert here and you can also remove the background color which I often do but everything's white so it's okay in this case just going to go to my downloads and then what we're going to do is I'm going to select it so now we're that's our logo that just got generated looks pretty cool so we're going to bring that in here and just bring it up here so feder's now got so we can use in the upper leftand corner keeping things simple we're going to remove those grid lines very very soon that looks pretty good I think this is a little bit big on that so we can go to on this font it should be like a 10 or eight or something yeah that's pretty good very good so our receipt we're going to move that over that's got to be inside the receipt we also want some information we want to be able to search for a row so inside here search receipt and also I would like to know whether it's taxable or not so that gives you the ability and then I want to know the tax rate if it's taxable what is that tax rate these three are going to be right Justified so we're going to drop this back down again we're going to pin it and then I'm just going to give these a background color here which is fine and then right justify them now I'm going to double click column here to make sure that we're act I'm going to highlight all of them and use control one once again and I want border all the way on the outside we'll use that solid black border and all the way on the inside but the only difference is I'm going to put that dotted line in the middle here oops well I'm going to undo that I don't want that third row so we'll do it one more time bord is all the way around an inside dotted line on the middle just those three rows is fine this one whether it's taxable or not this this is going to be simply a yes or no so we're going to go into Data data validation we're going to use a list and we're simply going to write in yes or no yes or no and we're going to call this tax option so I'm going to write in the name range tax option here this is going to be our tax rate so 0825 I want that to be a percentage so percentage but I also want to show multiple digits so this is called our tax rate we're giving that named range called tax rate and I'll show you why that's important all right so let's look at the totals here so in inside here I'm going to put my subtotal here I'm going to put my tax and here I'm going to put my total these are going to be right Justified so I'm going to do that right now right justify that here and what I oops all of them and what I would like to do is put that sub total in here so this is going to be a thicker line I'll just going to use control one and I'm going to put a bit of a thicker line uh maybe just a thin line no I think that's good so here what I would like to have is multiple lines here solid line on the border and Dot line in the middle and I'll proba be a solid in the middle too that's good I want to set these to currency and I'm going to left justify them or maybe not left justify currency is good maybe you keep them on the right what's going to be the subtotal is simply equal to sum of whatever is located in here great so we've got that there now we'll go over the tax in a minute let's get some information here the total is simply going to be a formula so if I put one and 10 in here I need to make sure that these are also currencies so we're going to set those two currencies so now what we have is going to have a formula equals and the reason is there's two conditions I want to make sure D and the row does not equal empty and I also want to make sure that the unit price does not equal empty if those two conditions are met then what do I want to do I want to multiply the quantity times the unit price so we're just going to do that times the unit price which is here otherwise we're just going to leave it blank so let's fix that up we got to have the and on that one so that's all we need to do let's do this if there we go got an if there we go so now what we want to do is I'm going to copy this here and then I'm just going to Simply paste that formula to all of the rows that are associated so I'm just going to paste the formulas I only want the formulas let's try that again copy this and then we're going to paste it down here pasting just the formulas which will be sufficient for our needs very good the item name of course we need a data validation on that so once again we're going to go back into the DAT of validation here we're going to use list and once again we're going to use equals I think item names was the num Eagles item names if we get it wrong I think it's item names or names good so now we can see we can just enter some data here and we have the desktop the idea is when we enter a name both the description the quantity gets defaulted and the unit price are automatically going to be entered so that's going to be very helpful I'm going to Center this and that's going to be VBA that's going to take care of that saving our work so far things are looking really good we can go into the view we don't need to see the grid lines anymore now what I would like to do is I would like to put the tax here and we have a taxable so this is either taxable yes or no if it is taxable let's take a look at this equals if our tax option equals yes then what do we want to do then we want to multiply the subtotal which is here and I want to multiply times the tax rate that we just created so that tax rate is here otherwise just show empty very good so our tax is 83 now equals the sum of both the subtotal and the tax here so I like that there and now of course if we change this to no then no tax is going to show up which is perfect very good I like it continuing on let's do some conditional formatting here as we add rows to this what I would like to do is I would like to have just a little bit of a dotted line here so to do that I'm going to highlight the entire cells and I'm going to go into the Home tab and then conditional formatting and I'm going to create a brand new rule it's going to be based on a formula and that formula is going to be equals to and we're going to use the first row in column B but we want to use every single row so we want remove the dollar sign does not equal empty that's the formula there so the format all we need to do is just put a dotted line and I'm going to put it on the bottom so that's sufficient and click okay and click okay so as we add items that dotted line will show up so it's a nice way that looks good so now what we want to do we've got the format pretty much set let's go ahead and set the format of this date here I'm going to use the short date format so things are looking pretty good it's pretty quick to create these we've created it uh in not too long all right so things are looking pretty good let's take a quick look I'm going to merge and center this and use it for a customer message merge and center and we'll use a bigger font and bold and italicize and we'll just put in thank you for your business in the exclamation point so I do want to put that in the center here looks pretty good so we got the note we've got the items and we've got the customer it's going to be up to VBA a few things we need VBA to do when I enter a customer I want that customer address in C4 and I want that customer email address in C5 when I enter the item name I want the description a default quantity of one and the unit price to automatically be entered and the last thing is when I search for a receipt I want to run a macro that's going to load whatever receipt number they've entered that's going to be up to VBA to do that I'm going to take a quick look to make sure that we're kind of done with the design this is a relatively simple design but it looks pretty good I don't think we need to do other than the button so why don't we add those inside now before we start working on that so I'm going to enter the shape here and we'll just enter a button here and we'll give it that default color which is this and I want to make sure that we add the font color of white here I want it right Justified once we get it we're going to call this new receipt and I'll use all in capitals so new receipt that's the button and I don't need a border on that so we'll remove the outline on that no outline I might increase the font a little bit but I think it's okay it looks good shape format let's check the size 1.4 is a good width 32 that's pretty good looking good we can probably go3 on that that we don't need that high three so continue on now that we have the new receipt I'm going to need a total of six buttons so I'm do contrl D and then we're going to bring this one down here and then contrl d one more time D D D D okay so oops that's one too many we'll straighten those out and get those aligned we want to save the receipt so I'm just going to change this to save and then I have icons saved for each one of these instead of a new receipt we're going to call this delete receipt we need one to be able to delete an individual also what I would like to do is of course we have to print it so we're going to print the receipt which reminds me we need to set the print layout on this and double check that generate PDF crl a generate PDF and lastly I want to email the PDF or email the receipt email receipt Let's Line these up so what I'm going to do is I'm going to use my selection tool here just going to highlight them I'm going to make sure that they're all in the middle and I also want to make sure they'll equal distance for them and including this one too so making sure that we have that that looks pretty good so we can remove that and I'm just going to move them in the middle things are looking pretty good saving our work it's time for the icons we're going to insert pictures and then going to place it over cells in this device and I've got them saved up so we're just going to pull them up here and I've got them right in somewhere here so receipt generator remember if you want all these icons or anything else of course I have that available on our patreon platform I'm going to insert everything that we have here while they're all selected I'm going to set the height to0 2 and then all I need to do is just bring them over then start adding them in so new receipts going to go right here here this one we're going to put for email this one I'm going to use for delete here we're going to line them up they don't have to be perfect just yet this one I'll used for the PDF this one for the print and there's one more somewhere around here here it is and then this is going to be for saved so what I'm going to do is that I don't want to line them all up so again using that that looks pretty good so now all we need to do is just Center them and then group them individually so we're going to put them in the middle group them I'm going to do this for each one of them as I hold the control down and I select each one and then I group them individually making sure that they're both in the middle then group we can also make sure that we do not want to move or size with the cells so we're going to set that up once we have the buttons then we just need to write the macros and then we assign the macro to the button so it's relatively once we make sure that they're all in the middle very good so now what we do is I'm going to use controll a what that's going to do is going to select all the shapes I want to make sure that I go into the properties here and set move but don't size with cell that's very important if I change the columns I don't want those button sizes to be messed up so again saving our work always a common practice to do that so we've got all of our buttons set up we're looking pretty good now what I'm going to do is I want to get some actions I want things to happen when I actually make changes the first of which is if I make a change to C3 and it's not empty I want to get the customer address and I want to get the customer email the customer address is located in column B email is located in column C so that's exactly what we're going to do do we're going to use VBA to do that if you've ever used VBA no worries I'm going to walk you through every step first thing you want to do is make sure that the developer tab is visible if it's not visible you can click on any tab here and just put customize the ribbon you just want to make sure that the developer is selected here so that's important once you do that here you're going to go into the developers Tab and look out for Visual Basic also you can use a shortcut called alt f11 alt f11 will also get you into that and I've got two workbooks open remember that actually I've got three which is our automated receipt generator but this is the one that we're going to collapse this one this is the one that we're going to be focused on here there's no code first thing what I would like to do here is I want to make sure that our code names are set to make things easier on coding so I'm going to select on the properties and that's kind of good practic is to do that right away oops I don't want to do that let's get rid of that go back into the VBA and make sure that we're setting the name this is what I wanted to do now you can pin your properties and they might be pinned or they might not be so it depends on what you're doing but no problem I'm going to then take these so here I'm going to select on it if you don't have your properties open you would just select here to click Properties or F4 is a great shortcut I'm just going to call this receipt and I can refer to that in the code with these names so it's very very important to be able to do that so we're going to put in the name and now what I want to do is for each one this one's going to be our receipt database so receipt database this one we're going to call this receipt item I'll shorten this one up a little bit Rec item database we're going to call this customers and they really help us when we know the names because when we use code sheet one or sheet 5 we really don't know what's on it so we're just going to call this item database or items I just keep it at items that's fine here so we've got every sheet name we can close this out what I would like to do is focus on our receipts so that's what we're going to focus on I'm double clicking that and I want to create an event it's based on when the user makes a change so it's a worksheet event I'm going to select worksheet then I'm going to go into the event of that's change so we're making a change this one selection change automatically gets added we're not going to be using that so we can clear that out we're making a worksheet change the first thing I always like to do if the user makes CH that's not what I want to do first I will do that in a moment I do need that if Target so if the user makes a change to more than one cell we're going to exit the sub out this next line is important but not h12 if the user makes a change to some cell what cell are we focused on focused on C3 so that's the cell we want to change but we also need to make sure that C3 is not empty so C3 Dov value does not equal empty so in those two conditions we want to do something and I need to put the then in there so then what do we want to do well assuming that it's not empty I need to make sure that we have a accurate customer so I'm going to Dimension a variable because I need to know what row that customer is on I must know I want to look up this customer I want to find the row then I want to grab the address then I want to grab the the email but knowing that customer row is very important so we're going to pull that up and we need a variable to help us with that so customer row has a long whole number variable so what we're going to do is I'm going to look for it but if it's not found that customer it's going to create an error but if I wrap it in on Air Zoom next and I wrap it in on air go to zero if you see this auto typing it's Auto hotkey that's helping me do that I have trainings directly on auto hotkey to help you with that so the customer row is going to be equal to something what we need to do is we need to find did so where are we going to look we're going to look in the customer sheet and if I've got the sheet name wrong and I put the period nothing's going to happen if I have it right it Intellis since it's going to pop up range and we want to look up the what I want to look up the customer name I do want to make 100% sure that I have this named range right so that's very important if we look in our named range we see that we have customer names it's plural so I want to make sure that I've got the name right so customer names inside that customer name I want to find something and what do I looking for I'm looking for what's in range C3 that is the customer name I'm going to look in Excel values and I'm look in Excel hole and I want to return the row what is the row number that it's found on if the customer row does not equal zero that means it was found then I want to do something what do I want to do inside range C4 do value I want to put something there what do I want to put I want to put the customer address where is that customer address located customer do range it's located in column B and the customer row value so this is our customer address likewise I'm going to copy this because it's going to be something very similar but it's going to be in C5 in C5 is going to be in column C and C is our customer email address I believe it is B is going to take the address C is going to be the email address so C is going to take on that and then we're just going to put email very good so let's take a look at that all I need to do is double click on this or enter something else and we'll just change this to range it's not ranges let's take a look and also we need to make sure that we use and if very good continuing on all right so things are looking good so we see we changed it if I change it to another customer it's going to load it all right I like that so that's the one of three change events that we need to focus on the next one is the item name if the user makes a change between B8 and b34 what do I want to do I want to make sure the item is accurate it has a row I want to put in the description of the item I want to put in a default quantity of one and I want to put in the unit price and it's going to come directly from our items database the description and the price are going to go in here so we can put this to unit price but it's kind of we get the idea so now that we have that let's go ahead and update that inside the VBA for that so this of course is on customer change on customer change next up we want something for item change so let's go ahead and add that in on the item change so add a space here we're going to write in comment on item change once again if the user makes a change to a certain range now what is that range it starts with B8 and it's going to end at b34 let's double check that to make sure b34 that is correct so b34 if they make a change we also want to make sure and range B and the row that they've changed which is the target. row. value I want to make sure it's not empty does not equal empty then we want to do something so let's update this to be a colon things are looking good now what I would like to do is once again I want to Dimension here our item row Dimension the item row just like we did for the customers as long and also just like we did with customers I want to wrap it in on a resume next and on aor go to zero because if it's not found it will create an error that item row is equal to the items. range and again once again double checking to make sure that we have that name range it's called item names is what we're looking inside to find that it's called item undor names that's where we're looking we're looking for what we I'm looking for whatever's right here whatever they've changed is right here so I'm just going to copy that paste that in Excel values excelo do row so we're looking for that if that item is found what row is it found in once again just like we did with customers if it's been found if the item row does not equal zero then we need to do something I want to enter the description the quantity and the price so where's it going to go range we're going to put it in column C and the target. row. value that's where we're placing it equal to the items. range so this of course is our item description and we see that our item description is found on column B so that's going to go directly inside column C so here it is it's going to be B and the item row. value so this is our item description we can copy this part here and actually I'm going to copy everything here and then I'm going to enter it here and enter it here now this one's going to be our quantity so I'm just going to put in one here and this is our item quantity and that's going to go in column d e is going to go directly from our unit price so we've got our unit price and now what we're going to do once again saving our work before we run it all I need to do is just double click on this and we see that our description the quantity the price looks really good if I change the quantity the price will change looks pretty good so we'll double click on each one of these double clicking click and click so we've got that it's looking quite good and the last one is when we make a change here to the receipt I to we want something to happen I want to load the receipt so that is going to be right down here we'll clean this up a little bit go down here and we're going to say on search receipt ID and that one of course is going to be based on what sell we're looking at sell I2 when the user makes a change to that so we're going to change this to I2 we also want to make sure that and range I2 we'll do this I2 dot value does not equal empty then what do we want to do I want to run a macro to load the receipt we haven't written that macro yet but let's write the name of it right now receipt load so we just need to make sure we give it that exact name then don't forget the then Randy all right so that looks pretty good so that's pretty much all the code we need to write on the sheet now what we're going to do is we're going to write modules to do everything that we have buttons for along with loading so to do that we're going to insert a brand new module so insert module of course you can do it from up here as well either way it's fine again once again even though we're going to have just one module I really like to give it names so we're going to call this receipt macras so we can write them and then what we want to do is we'll start writing them now sub receipt add new then we're going to do SUB receipt and then we'll just do save update so it's going to be the same one for both save and updating and then sub receipt load that's the one we just wrote so we need that I also want to be able to delete it receipt and then delete and also we need to print it so of course sub receipt and then print we also need to create our PDF so we need to do that sub receipt and then create PDF and last thing is we want to email it so here sub receipt email PDF so that's look good so we have all the macros we are going to use some variables that are going to be common so let's do that for all the macros Dimension the receipt row although we'll be redefining this in some macros that we use find as long last receipt row as long because we need to know how many receipts for example this one the last receipt row is 12 so we need to determine that as we Loop through all of the items so we need to know which is the last row I would also like to know the last item row as we move through items as long the result row as long we're going to Loop through some results so we need to know that and also let's Dimension some more uh the receipt database row as long receipt item row long the receipt ID it's going to be a long variable as long because we need to know the next receipt ID the last result row as long and lastly the receipt item database row as long or database RS we're also going to use the file name for the PDF so we're going to di mention the file name as a string variable that's going of be it for a while so the first thing is add new so the add new is relatively simple uh it's just going to clear the contents of the current and we're going to also default the date so we're going to focus on that receipt with receipt I want to make sure I've got the name right so I'm just going to use the intell sense to make sure it pops up we're going to clear some cells out so the first thing what I want to do is we want to clear out the customer name so that's C3 we can bring this over a little bit so we can start to see both C3 all the way through C5 I want to clear out those three I would also like to make sure that we're going to clear out B8 for the items all the way through remember we don't want to delete the formulas here so we only want to delete up to column e so it's going to be E8 all the way through E34 so we'll do B8 through E34 also what we're going to be doing is I want to make sure that we clear out uh any search here inside I2 so I'm going to use that I to also there's going to be some hidden IDs actually I should say hidden row numbers in column G you'll see them at first and then we'll hide them but I want to clear out what's in column G so from G8 to g34 I want to clear that out G8 through g34 do clear content so that's going to clear those items out we also want to get the next available receipt ID so that's going to be very important so what I want to find is the next available receipt ID and if there's nothing make it one so if it's found what we're going to do is we're going to use the max function we're using the maximum of all but if it's not found it could create an ER so we're going to wrap it on a resume next and on errow go to zero so to do that we're going to say the receipt ID is going to be equal to we're going to use a formula application. worksheet function and we're going to use the max function here and what's the max max is based on the receipt ID so we're going to use this named range here this receipt ID we're going to get the maximum of that if I know the maximum and I add one that's going to be our next reced ID so that of course is going to be in our receipt database range and then it's going to be the receipt ID that's our named range and then what I want is the maximum of that so here plus one so of course if there's an error it's not found like there's no data it will be zero so if the receipt ID equals zero then receipt ID equals 1 set initial receipt ID so that's pretty good that's going to say and what I want to do with that once I have it in the variable I want to place it directly here inside F3 so we're going to set that here range F3 Dov value equals receipt ID and then we're going to set receipt ID once we have that I'd also like to set the current date that current date is located in F4 so do range F4 Dov value equals date and that's our current date once we have that we are good to go so I think that we're easy on that I'm going to save our work I'm not going to run that now now what I would like to do is our save receipt so we need to be able to save it and so the idea is this we're going to use the save or update I need to look at this receipt number I need to know if it's been saved if it's found in this database and I know the row it's been found and I just need to update the date and update the total if it's not found I need to add a brand new row also with the item databases whatever items we add in here we need to save those I need to know what row they're on and I need to know what database row they've been saved at I'm going to place that database row here what I mean is let's say I save it right here I know it's been saved on row four so that means there would be a four right inside here like this if there's nothing inside column G then I know it has not been saved to the database so we need also need to make sure that we get the last row based on the 34 being the last one if there's no date at all they haven't entered an item we do need to prompt them for an item I would let them save receipts without a customer sometimes we don't have a customer name but we would require an item in this one so I'm going to require that so first of all with receipt we're going to focus on just that sheet I'm going to determine the last row so I think I've got a variable up here we'll use our last receipt row this is the variable I will use that variable equals and then we're going to use range we use column B and then 34 N XL up. row so this is our last item row so now that we know the last item row if the last receipt row is going to be less than eight then let the user know message box please enter at least one item before saving we can exit out of the sub great so we want to make sure they have it now what I want to do is I want to know if this receipt has been saved or not yet we're going to use the find command just like we did before and if it's not found it will create an error once again so we're going to use the on air resume next and on airor go to zero so the receipt row is going to be equal to our receipt database. range we're looking in the receipt ID once again just as we did before find we want to look for it what are we looking for I'm looking for what is directly inside F3 that's what I'm looking for so finding range F3 is that receip ID value I'm looking at Excel values an Excel ho and I want to look for the row great so that's going to tell us whether it's been found or not if the receipt row equals zero then it's not found else already saved it's already set up so we don't need to do much there if it already has been saved but if it's not then we need to let them know so let's continue on so if the receipt row is zero then of course we need to make sure that we do have the first available row so the receipt row is going to be equal to receipt database we need to get that range and then we'll just do a not large row do end XL up. row plus one so that's our first available row and we probably don't need this so what I'm going to do is just inside this here our first dat all I'm do is going to put that receipt ID here we've got the next one right here so a and the receipt row. value equals receipt ID we're just going to put that directly in here so we know that what is that receipt ID where's it coming from it's coming from right here F3 value we didn't put it in the variable yet so that's fine receipt ID so here receipt ID I'm just using minimal code on this training here so we don't use a lot of variables so that's all we need to do next up all I need to do regardless if it's new or an existing is just put the date and put the total in so that's pretty much it so we're going to do that right now that's going to come directly in column B I'm just going to copy this here makes things a little bit quicker and we're going to put it in here B of course is our date so we're going to put in the date where's our date coming from it's coming directly from F4 so we're going to change this to F4 and that is our receipt date so we're just going to put in the receipt date and actually I did forget something I'm going to insert here uh not here insert here I want to put a customer in I want to save that customer name that's fine we're going to put the customer in C I like that better customer name is going to come directly from C3 so we're going to put that inside column C once again we're going to paste this in here in column C it's going to come from C3 and that's going to be our customer name lastly is our total our total is going to go into column D and our total is going to come from where our total is coming directly from f37 is where our total is So f37 is our receipt total so here we're just putting in receipt total great so that's at least the save the main information but now we need to focus on our items we need to take all these items and make sure that they are saved or updated inside our receipt item database so let's get to that now what we need to do is we need to to run a loop so let's put a little bit of a comment here called save or update receipt items and to do that we need to run a loop for the receipt item row equals eight we're starting at8 to the last receipt row we've already determined the last receipt rows here so we have that and we just need to close our Loop and we're going to save the information so next receipt item row and the first thing we want to do is determine has it been saved or not we're going to look inside column G if G contains a number it has previously been saved if do range G and receipt item row. value equals empty then it's not saved else already saved to database so if it's not saved I need the first available item Row the receipt item database row is going to be equal to receipt item database I think that's yeah that's the one range a a 9999 n XL up. row plus one so that's our first available database Row first available row I want to take that first available row and I also want to put it directly inside here because we've now saved it or we're about to save it so I put it directly inside their value equals our receipt item database row so we're putting it inside G next up what I want to do is inside column A let's take a look at that inside column A I want to put the receipt ID that's for only new items I want to put the row and I want to put the database Serv so three of these columns are only for new items if they've not been saved if it's been saved before or new I'm going to put all this information in regardless if it's new or not so a g and H we have to enter now only for those new items so I'm going to copy a I'm going to place it right down here and the database receipt item database row I know I'm moving fast on this but I want to make sure to cover as much as possible to make sure that you get it all in of course you can slow down the speed or of course you can watch it as many times you want a is going to be equal to that receipt ID now where is that receipt ID it's located in F3 so equals. range F3 Dov value and that's our receip ID once we have that also what I want to do inside column G is place that so we're going to place that here actually I'm going to copy all this it's easier I'm going to copy this and then I'm going to update it so G is going to be what G is our item row it's this one right here this one right here as we move we need to save what row on our receipt that's been save it's right here this is our receipt item row lastly I want to put the database Row in so the database is going to go on column H that's going to tell us what row it's on and that's simply just a formula so we're going to put in equals and then quotation marks equals and then row that's it set database row so those are only for new items so else what do we need to do all I need to do for existing is grab the database row right from here so that would be our receipt item database row is equal to whatever is in G we'll just put existing database row once we have that everything else is regardless of whether it's new or it's existing and basically I can use it a one line of code I need to take whatever is all in here and I need to put it directly or update it directly inside here b c d and e so that's going to be here so we're doing something like receipt item database. range starting over with b and our receipt item database row and all the way through our total which is f again once again and the database row and receipt item database row value equals what do range it's coming directly from our receipt which is here it's all coming from B all the way through e and so it's starting out at B and it's going to be our receipt row here so we're going to put in receip item row and let's bring this out a little bit shrink this down we don't need this and bring it out so you guys can see everything as we move the screens around a little bit we also don't need to pin this anymore for now we won't be using it too much doing some work around there so we want to make sure it's going to go all the way to we're going to use quotation marks to F right so colon F and of course our receipt item row. value so this is just called item details so we're saving all the item details all right so that's pretty much it we just need to add that all in so as we Loop through that we want to make sure we're going to close our Loop here and then I'm just going to put a message box that it's been saved so message box receipt saved very good so that's good and you know whatever issues we have we'll fix them right away so don't worry about that now what we want to do is we want to load them so what I'm going to do is remember when we wrote them macro anytime there's a change here it's going to load and how do we know that let's just put in message box test to make sure things are working that's good so that means when I make any change here it's just going to load up test and that's exactly what I want so we know that this macro is going to run as soon as we run something but what do we want to do well the first thing what I want to do when we load it I want to make sure the user has entered a correct ID number so to do that we need to check that actually I want to clear anything else that we might have I want to clear all the old data when we load it in so C3 all the way through C5 that's the customer information I want to clear that I also want to clear out B8 all the way through E34 just so like we did before and I also want to clear out 34 not 24 34 I also want to clear out column G8 through g34 good so we've cleared out that information do clear contents now what we want to do is I want to check to make sure we have the row we already wrote it up here actually we've written it up here just like this we want to find what's in F3 and we want to see what row it's on just like we did here so we can just copy and paste that here now if it's not the correig row if the receipt row equals zero then let the user know message box please enter a correct receipt ID then we're going to exit the sub okay they did not enter correct one if we have the receipt row we can load the information in so that's no problem so how do we do that range first thing what we're going to do is we're going to load in the receipt date so we'll just go in order so if we see the first thing is the date from B we already have the ID that's already located in there actually what we can do is when they searched for it I want to take whatever is an i and I do want to place it in F3 so let's do that first so F3 Dov value equals. range whatever is in I2 I believe I2 that's correct do value so we're going to place re seip ID so next upep now we want to load from the database so range we're going to look inside that date which is inside f4v value and it's going to come from the database receipt database. range and column B and the receipt row receipt row. value and this of course is our receipt date next up is the customer name we want to make sure so I'm just going to copy this a little bit quicker here customer name is going to go directly inside C3 so I'm going to put that C3 and our customer name is going to come from column C now the total we don't need to load total it's automated customer name right we would never want to load the total in here because it's a formula we don't want to replace that it's automated so the total doesn't need to low it's only one way so once we have that we're going to focus on the items now so that's all we need to do to load in the main information so now with receipt item database I'm going to load in those items to do that I need some information so you see here in F3 I want to load in all the items so this received item database I want to run an advanced filter so I'm going to put this somewhere around here I'm just going to paste this here and I'm going to call this criteria and I want to load in so what do I want to load I want to the item name the description the quantity the price the total I don't need so I'm going to paste that right here and I also need the database row I'm going to paste that in here when you use Advanced filters It's alw