Create and Manage Your Bulk Invoice Generator from Excel for Higher Education Effortlessly

Bulk invoice generator from excel for Higher Education

well greetings this is Trev once again this is our final tutorial on the invoice to PDF program I'm going to take you through in this tutorial and show you what the code means that you'll be adding to be able to move the data around the database and to filter the data so at this stage of our tutorials we'll find that we're up to if you go to the website and we'll open up the website here show our table of contents and we're going to go to part five which is VBA code for modules we've added all the object code all the sheet code and also the protect code so click into there it'll take you down this is the video we're making now the one that that says still to come and now we're going to have a look at the code that goes in to copy all the data over and to run all the filters to create the PDFs all of that information I'll run you through now now you you can just copy this code over and pop it in and I believe it will work for you but when I take the time to be able to see what the code is doing and thus you'll be in a position to start to understand a little bit how you can change it perhaps to suit your needs so let's go to the program and get started grab the application hit alt and f11 to open up the Visual Basic editor now we're going to look at modules only and the first one we'll be looking at is the copy to module so double click on the copy to module go to the website and grab this this piece of code now I've run comments all the way through here to show you what this is doing in essence let's go through it first of all it's unprotecting the sheet so we'll minimize this so you can see the sheet at the same time so the first thing we're doing is unprotecting the sheet we're dimensioning a couple of ranges a destination and a source range now the destination range basically goes to sheet 4 which is our database sheet or our all inv voices sheet here it goes down to this column here finds the last available cell that is in here offsets it by one and then pastes in our data all the way across and if there's multiple ranges in here if you have 10 or 12 entries it'll put them all in in one hit that's what it does so here's the code to do that first of all it asks you though have you got all the data in if there's anything missing we run some if statements to be able to ascertain what is missing there's something missing we stop the user from proceeding then there's a message box a select box that basically says are you sure you want to do this so gives the chance the user a chance to opt out now all the way through this code you will notice I have put in comments these comments will help you to see what the next piece of code does now you'll notice that we're copying a range called invoice where is the range invoice well the range invoice is a dynamic named range that is on our invoice sheet we're copying that and then we're going to our destination range and pasting it in and then we're running a sort to sort the data now let's have a look at where the invoice range is well I've hidden it here on my sheet I'll just unhide it for you to see here it is over here and as you start adding data that is the range now let's po po some data in here and you'll see that it over here we'll put the date in now as soon as we did that the formulas allowed the data to run over that's the invoice range and if you have a couple of sets of data there we'll add some more in you'll notice there's two sets of data in there let's go and check that with our formulas we'll go and find the formula manager find invoices and you'll see that that's the range we're copying over invoice here it is here that's the range now even if you have some blanks in here it's still going to pick it up the formula we're using is an index formula and with a match offset I'm sorry and with a match to do that so that's the range we take that range and copy it over and we paste it into here and then we sort it out so basically that's the most important piece of code in the whole application if you can understand how that works everything else is going to be a little bit easy now this next piece of code is called items this particular piece of code just sets the static Nam ranges resets them whenever we add new items to our list all right so that's very easy it just goes down finds a range and gives it a name some of them are multic columned and some are singular our first two here are multicolumn and next two are singular and then we're sorting now for our cascading data validation to work we need our category to be sorted and that's what we're doing here right at the end of that every time we add we have the ability to be able to on our all invoices to filter our data we can set the data up here we're filtering it in place by means of two Advanced filters one Advanced filter will work across this section of data here this will be your um area that you reference the criteria for this is over here to the right here it is here the other Advanced filter runs just off an invoice number so you can pop in and find any invoice number that you want and it will filter that and put it into here now we're using these Advanced filters in situe or in place which is something that we haven't done previously so we need to clear the advanced filter or show or Data before we run the next filter the two Advanced filters are are basically the same as what we've used many many times before if you haven't had a look or haven't done too much with your Advanced filters in the past by all means used them look we actually filter sometimes by changing the criteria um we filter hundreds and hundreds of sets of data and you can get some fantastic results by just running multiple Advanced filters one after the other in this instance you will notice here that we set a named range called all invoices before we run the filter so we're going to pick up what data is in there and then filter it so we're picking up so this is our data set here if we have added new information to it we need to pick it up so right at the very beginning before we run any filter we're using we're setting the named range all invoices we're making sure that our data set is always up to date before we move on and we're setting that as a static range we don't want any overheads with the offset formula here so we're setting it as a static range right from the out it so every time you go into this sheet and you want to filter it you're going to have the latest named range and you need that and then it simply runs your Advanced filter based on the criteria there's two of these filters in there they should work fine you will notice there that we're resetting our data before we run the filter here's the piece of code to do that I've mentioned that also on the website for you so the advanced filters have a look at them have a go with them they will filter both by invoice number and by customer and between any two dates now the next next thing we're looking at is our products when you add products we're going to be looking up the products based on a unique ID you can put this unique ID in yourself but it must be unique or you can create it in here and adding products all it does is takes this range of data that we have here the range through to here and then pops it in and adds it to the bottom that's all it does that's this piece of code that you're looking at here add stock products it takes the info from that row up the top here with the unique code and then pops it in to the last available and then I believe it sorts that data for us now you'll notice at the bottom of that it calls the macro items and that macro items sets the named ranges and then sorts the data for us where's the here it is here here's the the engine driving it it finds a range called add product and then just finds and D it into the next available and free row at the bottom okay well and here's a manual sorted if you want to sort manually this is for our sheet two for our product sheet there's a manual sort there as well and next code in the copy to is our copy credits now this again it is going to um copy the credits down and put them into the database set it does things a little bit differently it has three Source ranges and then it has uh one destination range it finds the destination range and then Pops in the three pieces of data so you'll notice as you go down here they are here paste special paste special and paste special I'll go and take you through that's over to here what that is doing this piece of code is taking the information here from your credits that you're putting in so we put in here a credit say from the 13th of the 3 of the 1 13 we add our credit in here a credit amount it's $12 and we know this what is happening here if you notice a little formula up the top here it's converting it to a minus because our credit is going to be taken away so it's going to go into our database as a minus and so we put that in there's a couple of credits down the bottom here just show them to you here they are they've gone in as minuses so that in our totals at the top you'll see that our total will be the correct figure it will take away the amount of the credit for that customer right this is the code for move it now this uh this piece of code takes the data from here if you put in items into here you don't have to put them in continuously you can just put them in and then it sends them over to the invoice which is what it's doing here it's going to the invoice all of those items quickly sent over so we just clear that off we don't need to see that and uh I won't go through the code too much it's U not as simple as the other code but it will work fine it also has a built-in safety mechanism if you don't have room on your invoice to send all of that over it will tell you because it does a little bit of a quick calculation before it sends it over and if you got too many items it will kick it back at you now the print it module these are the the code for printing the information so if we go into our invoice and we say we want to print the invoice right which is what we're doing here we give it a name whatever you want and a destination and then say yes save it to that destination and that's the invoice a blank invoice we sent in this instance now I've set this up that way so that it'll work when you download it and use it and add the code but for many you might want to just send the invoice directly you might have a folder to send it to quite easy to do we just need to change the code a little bit to do that with our print invoice we would change this open dialogue section here we would just say a file path name and then we change it down here as well if you want to know how to do that uh what one user wanted was they wanted to actually have a cell reference become the file name and send it directly to a folder easily done contact me and I'll show you how to do that so there's two of those one is to run the PDF off here and the other is off our filtered data here so we can create a PDF here as well and look really that's about it that's the application what you'll need to do is go in and assign the macros assign the right macro to the right button so what you do is just right click on the button find your macro and this one is filter invoice so we'd find the right macro click on it and then just simply go okay it's as easy as that you'll need to do that across the sheet adding credit for instance here you go this one is called copy credit so you find copy credit and you go okay and that's going to work just fine so that's all the application put together it's not as complex as some of the ones we've done before if you've done some of our other more basic tutorials you shouldn't struggle with this but why not take the time to see what the code means and you can then modify it to suit your own needs this is Trev from online pc learning.com thank you very much for visiting my website and for participating in these learning projects bye for now