Get Your Fully Automatic Invoice in Excel Download for Research and Development

Fully automatic invoice in excel download for Research and Development

hello this is randy with excel for freelancers  and welcome to the reoccurring invoices   in this week's training i'm going to  show you how to automatically hands-free   create unlimited invoices at any interval  including days weeks and even months at a   time where you can create these unlimited invoices  and not only that once they're created we're going   to be able to email save the file and print out  all without ever touching your computer it's   going to be a fully automated system i cannot  wait to share with you so let's get started all right thanks so much for joining me i've got a  really fantastic training every once in a while we   need to create invoices automatically regularly  every single month every single week every six   months whatever your interval is you're going to  be able to do this not only that even if you're   not creating invoices i'm going to show you how  to create intervals for any type of application   whether it could be scheduling or could be  invoicing or billing or any type of recurring   instance whether it's estimating i'm going to show  you how a special formula do just that whether   it's any number of days any number of weeks any  number of months at individual intervals i'm going   to show you how you can do that whether it's from  every first second third or even every last day of   the week even every monday or tuesday or wednesday  or a specific day of the week of a month i'm going   to show you how to do that and a whole lot more so  we do have a lot to cover i create these trainings   each and every tuesday absolutely free for you if  you like this application you can download it at   no cost using the links down in the description  either with your email or facebook messenger   please don't forget to subscribe that will help us  out and also click on that notification icon bell   i'd love to hear your feedback your comments your  ideas and i respond to each and every one of them   right down below this video whatever social media  facebook or youtube or wherever you're watching it   go ahead and comment below let me know what you  think you have any ideas for upcoming training   how can i make this better or maybe you have  a feature fix or focus if you're on patreon   platform every single week i create a brand  new updated version of whatever i'm doing   for example a few weeks ago we had a salon and spa  manager application if you watch this and then i   added a bunch of things to this application for  our patreon so you can see now this little dollar   sign here i added that buzzer designates what has  been invoiced already so we have that i also added   a brand new pop-up calendar we added a set date so  we can set the specific date we can do a calendar   we have a refresh icon and we have a brand new  chart user form we can add a customer now simply   by adding this we can add a brand new customer  so that's on our patreon this updated version   is on our patreon so i hope you'll join us there  we've got a whole lot plus code books and a ton   other things for you there so let's get started on  this week's training with the reoccurring invoices   now we've done an invoice before so we're not  going to go and do a great deal on an invoice   this invoice looks familiar i have a invoice from  scratch where i go over every step of an invoice   that's a video called invoice from scratch so  if you want to know how to create this invoice   just like this that video there this video we're  going to focus on a few different things we're   going to focus on how to make anything recurring  at almost any interval we're using an invoice for   example but you can take the techniques and the  formulas and the code used here and transfer to   any kind of application so it's really important  so we're going to get into that right now and show   you what let's do a little bit of overview of  what we have inside the setup screen i've got   a frequencies days weeks and months i decided to  add that to a named range i've got weekly options   every day of the week and i've got some monthly  options we've got every last maybe we wanted every   last day of the week or maybe it's a specific  day every first second all the way up to every   31st day of the week so this is going  to be from monthly options i've also got   days options this is going to help us out i want  to know is it every last day or every first monday   so these are monthly options also those shouldn't  be the frequency so every last day or every   first day maybe it's every first day of the month  or maybe it's every first monday so we've got that   and also we have an end or on after we may be  adding that okay and also what i want is the   email variable so inside the email that we create  we want some variables we may want to put that   customer name email or phone number the invoice  number the date the invoice amount the staff that   was assigned or the current date and then also  on the invoice itself we're going to be able to   charge taxes yes or no the tax name and then the  tax percentage so that's just going to help us   out i've got some service items here this is just  kind of just items that we can add to the invoice   nothing very special about that the invoice here  invoice list this is a list of completed invoices   here the invoice template database now this i'm  going to go over in detail these are for the   invoice template so what that means is when you  create a reoccurring invoice we have a specific   template that is assigned a specific invoice id  and then all the associate items of frequencies   of how that goes in the template so all this is  going to help us reoccur that invoice created   automatically and then what settings are get  created what email gets created what gets printed   where does it get saved and all those details are  saved in this table here the invoice items are the   individual items saved on an invoice so if i  create invoice number five that contains two items   those get saved here or invoice number six that  can get saved here all right also we have a list   of clients just a customer name nothing very  amazing about that staff just an employee list   that i brought in we don't really need all this  detail on that but it's brought here so that's it   so we're gonna pretty much focus on a few things  we're going to pretty much focus on this invoice   screen here and this template database so we're  going to zero in on that focus so that's what i   want so basically what i want to do when i run a  macro i want to then check for all the invoices   that need to be created based on their date  so when i run that macro and check it's going   to check and it's going to automatically create  invoices based on what's scheduled that's going   to create an email for that and it's going to also  print it out to my default printer which is snagit   here it's going to print that out and create an  email now we just have one obviously we don't want   to create too many but basically it says hello  you know thanks so much we can have the dynamic   content inside the email including the name the  invoice and we also have thanks so much for order   we have invoice number seven here the invoice  number the total right and i'm going to show   you how to format these notice how these amounts  are formatted right and so we have that should   you have any additional questions or concerns  fred fretters at first federal finances okay   sounds good so we've also of course got the  attached invoice here so inside a pdf format but   that's kind of pretty standard and straightforward  so we don't need to go into a lot of detail on   that invoice so we get to create an email now  those details of how we do that are located here   so when we decide to make an invoice reoccurring  or not we can select this option here and it's   going to use conditional formatting to let us know  so we're going to go over every detail of that so   we've got a reoccurring details which lets us know  how often we want to create this every day week or   month a day so we can do days and our next invoice  date so if it's every one days or every 10 days   we want that next invoice date to automatically  appear today is the 17th of december so of course   next invoice date would be based on 10 days from  now so we want to create every 10 days or maybe   we want to create it every single week so maybe we  want to do every one week on monday so it's gonna   be the next monday is here right maybe we want to  do it every two weeks so that date is automatic   invoice date is automatically going to update  ingly to the number of weeks or maybe we   want a monthly option now monthly option is really  great because we got tons of monthly options so if   we set every one month on every first monday of  the month or we can do every let's see that's   that drop down list here maybe every last monday  of the month or every last day of the month as we   add those it's going to be let us know how many  days before and i also want to know how often to   create that invoice i need to make an update here  meaning when i select either day or week i'll all   adjust conditional formatting i also want this to  appear i want that to appear regardless i'll be   making that update during the training but i want  this to show up too with conditional formatting so   we want to know how many days uh before we want to  create this so what does that mean okay so let's   say i want to create an invoice every month on  the last day of the month now we know that that   invoice date is going to be on november 30th the  last day of the month right every one month or   every two months or however long we have it  so if we go every two months it's going to   be the last day so on this one how do we know we  want to create that invoice when do we want that   actually invoice to be created i want to create  it how many days before the next invoice right   because we want to get it out to the customer  so we can create any number of days before   that or we can create it on the same day if we put  zero okay so that's going to let us know how many   actually days the invoice actually gets created in  our system it is still going to have the invoice   date here and or maybe we want it on the let's say  the first let's say let's say monday of the month   so we won't track that so one under the first  monday or first right we don't want to end up on   a weekend so we wanted first monday or something  like that we have a lot of choices here to do that   and i'm going to show you how to do that once  the invoice gets created once it gets created   we also want to then what are we going to do  with it once it gets created are we going to   email it to the customer are we going to save it  to the file are we going to print it out we have   all these options here we also maybe once it's  saved what name do we want to save that invoice   we can use lots of dynamic content to specifically  name that invoice or we can just put any text in   here we want right so maybe we want an invoice  number or maybe we want to set it to the client   name and the invoice number here so we can do both  of those if we want to you know we have client   name we can expand that a little bit client name  and the invoice number we can do that also so we   have dynamic naming of the invoice which really  helps us make sure that our invoices are always   unique so that we don't replace or duplicate the  file also we have a save to folder we can browse   for that folder and save it to anything we can  also set an email subject here that email subject   can be also contained those that dynamic content  based on what you know the these information the   variables inside our setup screen and we also have  of course an email message maybe we do we want to   display that email first right do we want to show  up or do we just want to send it directly so we   can do that once we save it we can then save those  settings and it's automatically going to save for   that template so we have an invoice template so  when we create a new invoice we can create a brand   new template for that or we can use an existing  one right so that's really really a lot of   really details that are going to help us out all  right so let's get into some of the formulas and   see what we have and we'll start out with a little  bit of formulas then we're going to move into some   of the code and then we're going to move back to  the formulas that's we're going to do this won't   be too long of a training because i really want  to zero down we're not going to cover a lot of   time on this invoice itself because i really want  to focus on the reoccurring portion of that which   can get a little uh detailed so i want to go over  very very slowly all right so let's take a quick   look in here now we also want to know a few  things we want to know the row of the invoice   of course that's very important right we have  an invoice list here i want to know the row   where it's located in voice number one is  located on row three seven is located on   row nine so that's important but i also wanna  know if there's a template that's been created   i wanna know what row that template is notice  that i have three templates i've got a template   for invoice number five invoice number six and  invoice number seven here so they're all eight so   that means that i can make each one of those  reoccurring and create new ones based on that so   we also want to know the row if there's a template  we want to know the row of that so back into our   invoice i want to know the invoice template row  and this is based on the name range called invoice   template id using match and we have an invoice  template id here so this is means this is then   our our next invoice number so let's take a look  at this if we click a new invoice it's going to   clear everything out and then our next invoice  number is going to be invoice number eight here   and we have no invoice template right but if i  select a specific invoice let's take a look at   five i know we've created a five five this  this particular invoice is also a template   meaning this can be made recurring we've saved  the details up here so notice all the recurring   details loaded with this specific template so we  located here 5 is located on row 4 of the invoice   template we're using that match id matching  based on what is in b8 that invoice template   invoice id and we're going to use dot 0 and  we're going to add 3 now why are we adding 3   because this we're looking for this i want to know  that 5 where is that 5 found and what row is it   found on it's found on row 4. of course that's the  first it's our first match we want the row not one   so we're gonna add three i wanna know where those  invoice template details all that you know how   often the frequencies and everything like that i  want that to load it is separate from the invoice   why is it separate why did we just add it here  because not every single invoice is going to   have a template right not every invoice maybe  you have one template and you want to create   five invoices from a single template therefore  they're in separate databases right the invoice   list is separate so we also want to know if it's  recurring or true so we understand how we get   that row this invoice is simply a named range a  dynamic name branch based on these template ids   right here based on this here all right so next up  i want to know is it reoccurring see here inside   the format controls it's tied to cell b2 here  it says b2 okay so it's tied to b2 so we know   as we do this now we also have conditional format  that we're going to go over in just a moment so   we also want to know the customer row that's  associated we have a we have a customer that's   located here if we were to change the customer  that row is going to change it's also using the   match formula and we're just going to base it  on the name range called client name whatever is   an i5 and we're adding two because our customers  start in row three relatively simple the invoice   row this is the invoice that's been selected the  invoice row the the invoice that's been displayed   here is located right here invoice number  five it's based on l2 l2 so if we take a look   inside this invoice row it's based on l2 we're  searching for the invoice id the invoice id on   rule 5. that is located right here invoice id  located in row 5. so we see here that we have   row 7 excuse me invoice 5 is located on row 7.  so that's exactly what we want we want to pull   that row up so we know what that is if there's a  search row what is this if i decide i'm going to   search for a specific invoice like invoice number  6 i need to put id inside n2 once it's done it's   automatically going to load up that so to extract  that i know you need to know using another match   same formula here based on match base but this  time it's based on n2 because that's where the   user is going to search notice it was very quick  if a row is found if it's not empty then we know   we can load it all we need to do is just take  that search invoice that number here and place it   directly in here and then load the invoice because  it is a valid but of course if it's not if there's   no invoice it's going to say invoice not found  please enter another invoice because we don't have   one there okay because you can see here that the  search row is blank because it's not been found   okay the next invoice number well that's simply  based off the max of all the invoice plus one so   that's going to be the next one so if i create a  brand new invoice we want that eight to be brought   directly over into l2 because that is the next  available invoice number all right also what i   want to know if that invoice is loading i need to  differentiate between when we're making a change   here or we're actually loading an invoice so if  i load an invoice we're going to see information   load up here and i need to differentiate between  this type of change here and this type of change   so that we can load up our invoice items so  when we have two types of changes to an invoice   one manually by the user and the other one by vba  that's loading the invoice i need to differentiate   between those two types of changes and to do that  we're going to use invoice load this becomes true   and then it becomes false as we load the invoice  well i'll show you that a little bit later on   all right so the invoice template id we went over  this is the template id right so as we load it if   it's a template if it's been found we're going  to put that template id right here because we   know it is an original template this invoice  is an original it does contain the reoccurring   if i were to create a brand new invoice from  that we would know that that would not have one   i also want to know different settings so notice  we have email save and print those are all check   boxes based on that so it's simply a check box  based on those things okay so we have all those   those go true or false based on the email save  and print these are all based on the frequency   so we're going to get into those in a little bit  so we're going to cover this a little bit later   these are all formulas and functions based on  calculating the next date for that so we'll get   into that but as an overview just an overview  brief overview basically the weekly frequency   if there's a weekly frequency we would need to  know we also want to know the monthly frequency   so when i select months here right and we select  every first we know this is the first the last   option right would be one and one and one so  one weekly frequency is this looks like weekly   weekly we see we have days of the week so monday  is one tuesday is two so you see b12 change to   3 so weekly frequencies based on that monthly  frequency we have two options under monthly and i   click monthly we have two options this option and  this option here so when i select every last we   know the first the first option every last we're  going to return a 1 here so it's based on a match   formula based on those options if i change this  to every first of course it's going to change to   2. if i change our our first option is day our  second option is money so this is going to change   to 2. so these numbers are very specific to  these options that have been selected here   the days before is simply just whatever is in  here and then also we have some more formulas   here which we'll get into and then also i  have the email variables when i load an email   i need to know all the variables associated with  that email so we can do that here so if i want to   know the client name well that's going to be here  simply there what's located in i5 if i want to   know the email i'm going to use the index i've got  a named range we're going to index client email   and we're going to base it on the client row  that is where b3 comes in handy if you see b3   remember that is our client row so we need  to know that row and of course we're going   to subtract that about those two additional rows  and we're going to do that first column that's   going to get us our client email we also want  a client phone number which we're going to do   so i need to use index on that and also i need  the invoice number that's simply going to be   whatever is in l2 i need the invoice date that's  going to be whatever is in l3 so we just have   just basically link them to these fields here but  we want them all in a compact here because when we   add them to our email or names or file name we  can simply run a loop from 28 and i'll show you   how that works the invoice amount linked to l232  so it's going to be down here l32 and also what we   have is our invoice staff if there's been a staff  specific to this invoice located right here in k8   that's going to be linked out and lastly we have  our current date it's just going to be equal to   today so the idea is when we create these emails  what we want to do instead of saying client name   here we're going to replace that client name with  the actual client name here when we have a lot of   variables what we want to do is we can use just  a few lines of code all we have to do is use run   a loop from 28 to 35 we're going to look anywhere  where client name is located in either the subject   or the message and if it's found we're going to  replace it with whatever's name is located here   and that's with a loop okay great so that's a  pretty much summary of everything we've got going   on now of course we're going to go into a lot  of detail on these formulas here in just a bit   but i wanted to go over the code and of course  i want to go over the conditional formatting   that we have here so a lot of the conditional  formatting is based on when this is selected so   when i select that all the conditional formatting  turns off so you can't see it and that's based on   b2 being false so if we take a look inside here we  go into home and then conditional formatting one   of the rules is basically just that so when the  formula here is b2 equals false what we're going   to be doing is we're going to be putting the font  any fonts giving it the same that light turquoise   here in the background so we want that font the  same as the background i also want any borders   to i want to select none of the borders and then  the fill is going to be that same black that same   filled out color that turquoise here so we're  going to fill that out and clear that that's   going to hide everything that's associated as long  as we then apply that to all the cells concerned   which is all of these here if we see it it's going  to clear that as soon as we click uncheck that   that's going to go to false and it's going to  hide everything associated all right so what we   have some more there so when i click here actually  i want to make an update here when i click days i   want to show this one so i'm going to go down the  conditional we're going to take a look at some of   the managing rules in here and we have this one  so we want to make sure that if b2 is false we   know that's going to be true but this is the one i  want to make an update to and i want to make sure   that we are showing both uh this one here applies  to take a look at if n equals months right months   is i want to show the one above it only but  showing this one so let's take a look at that   this one is going to have to be updated to just  six only so f6 to d6 applying that because we   only wanted to apply it to the row above so we're  going to apply that now we can see it so there we   go right like this and we'll also fix that line up  so how do we do that we want to make sure that we   don't hide that line so we go back into home we  can add a brand new rule conditional formatting   and manage rules right here we go and we can just  edit the rule we can edit this rule or we can   actually cancel a new rule we do a formula here  and we'll do let's formulate here as we do that   gonna add in a new formula we're concerned  with what that it's not false so let's do   this it's gonna equal true right when it's true  i want to add that line above so that's going   to be centered that's going to take precedence  over everything else so clicking ok and okay and   then we have that line above okay so that's just  one so now whether we select weeks or months we   always get that but months is going to add this  additional line so all right so let's see when   we have days what do we want we just want to hide  everything so if i go into conditional formatting   back in there and manage some more rules we  take a look at a few more rules that we have   okay so we'll take a look at this rule here we  edit that we see that if f4 does not equal weeks   does not equal weeks then basically what we want  to do is we want to use that same we're going to   format the same one and basically that's going to  be based on row from d5 so basically when as long   as it's not weeks if it is days or it's months we  want to hide that right we don't need that every   first monday because that is based on weeks so we  want to hide excuse me five this right here this   one which one we're hiding well let's take a look  again when we select weeks i want to hide this   based on row 5 right there's no reason to see that  because that is only for weeks on wednesday this   option is only for weeks so that's why we have it  there and ever this option is only for months so   let's go back into conditional formatting is  only for months so let's take a look if it does   not equal months now basically if f4 does not  equal months then we're going to hide that row   that specific row row 6. so it applies to row  six here from d through f so basically we're   just hiding it so we only want to show however of  course seven and eight we're showing regardless   okay so that's just it there's just two rules and  of course if it's days we're going to be hiding   everything all right so that's the conditional  formatting that now we've got some additional   conditional formatting based on the email based  on the save to file and the printout doesn't have   any conditional formatting so let's take a look  now keep in mind that we want save to file right   that's really important whether we are going to be  creating an email if we're adding an email we're   adding a pdf to an email i need to know what file  name we're creating if we're also saving it to the   file i also need to know so save name is going  to be regardless of whether we are adding it or   not emailing it to customer can is is regardless  of that this is going to be regardless because if   we're going to email it to the customer i need  to save it to a specific folder before we can   email them out so that is important regardless so  let's go into the conditional formatting of this   conditional reporting and we're going to manage  those rules and take a quick look those three   rules here so if here in this case b9 equals false  and b10 equals false we're going to color in b9   and b10 what is that let's go ahead and screw that  over a little bit so we can see everything here   b9 meaning email b10 meaning save so if both  those are saved only then can we hide it right   if we are not going to email and we're not going  to save then we can hide these right because these   are important for either email or customer and  also if we're going to be emailing for emailing we   also want to hide all this if we're not emailing  so the conditional formatting that we're applying   there is pretty much the same you can figure it  out so we take a look right in here this one here   we edit the row we see if b9 equals false meaning  email then of course we want to simply of course   do use the same colors that we have but this is  going to apply all the way from d16 through f32   and that's going to hide all the emails associated  now i do have a little bit of a macro that's   associated with this that is going to hide this  folder icon save folder button this is our save   folder button it's going to let us browse for  that and this of course is going to be hidden   is if we're not going to be saving or emailing we  want that hidden also there's macros associated   with that so let's get into some of those macros  now that we've covered the conditional formatting   and let's go and see exactly how we do that  because we have some macros that are tied to that   so inside the vba developer using alt f11 will get  you there into the developers and here instead of   developers we just have two modules we've got the  invoice module here which we may go over if we   have time and the reoccurring which you're going  to spend most of our time here so remember we have   a save to file invoice save to file i want  to i want to show or hide that folder button   based on that so this macro here so when i click  right here here there's been a macro assigned   to that and basically going to be that specific  macro here so it's going to toggle that button so   this one saved a file we want to know if we're  going to hide or search and the same thing for   this so if we're saving it we need to make sure  that we're going to show it or not that's very   important so what i want to do is i want to go in  here if the invoice b10 equals true b10 is focused   on our save right if that's true if we're saving  that then what we want to do is we want to make   sure that save folder button is visible right we  need to browse for that specific folder if we're   going to be saving so we need to make sure that  that's visible otherwise we're going to hide it   that's all we have to do that's just a simple  toggle also we want to run the macro browse to   save folder right so that's the macro that ran  when i clicked that icon it's that folder so   relatively now all we need to do to make sure we  do that is we dimension here this one right here   save to as a file dialog so save to is  simply going to be equal to the application   file dialog and then we have a file folder  picker remember this is folder picker   if we're picking a specific file from that  folder it would be file picker so we're going   to set the save to it's going to be equal to  title is going to be set select folder to save   two i guess that should be two right and uh  we also want to allow multi-select false and   if for some reason they don't select anything  right the show dot show won't be negative one   we're going to go to no selection it's going to  skip otherwise all we're going to do is we're   going to take that selected that full file path  we're going to place it directly in e13 of that   folder so that folder is going to go directly  inside that file path right here okay so that's   it for that macro relatively simple macro on that  okay what about reoccurring toggle now let's take   a look at this we have a toggle here now this  toggle of course we need to make sure that we   do a few things if we take a look at that not only  is conditional formatted but we need to make sure   that that button that actually i got to show that  to make sure that it showed right i want to make   sure to show that folder on on toggling so we'll  make sure to show it but we only want to make sure   that we show it if the save is selected here  and b10 is true all right so we'll fix that up   because we want to show that so how we want to do  that so first of all what we're going to be doing   if the reoccurring talk if invoice b2 equals false  of course then we're going to hide both of those   things however if it's in this case right so let's  take a look at this this would be no recurring   and this would be reoccurring and i want to add  something in there that save boulder button should   be true right when we're reoccurring we want  to see true but i don't want to show it always   c true okay so we don't want to show up but maybe  we don't want to show it always why don't we want   to show it always well if we're going to show  it showing always so now i don't want to show it   let's take a look at that okay so we're fine we've  selected all of them it's good so we're selecting   all of them that makes it a little bit easier so  the user knows what they're selecting i'll leave   it just like that i like it pretty much there so  we're just going to make sure we show that folder   button and then also what i want to do is i've set  b9 to b11 true so what i'm doing is i decided to   set all these checkboxes to default to print  to make sure that the user has all the options   available to them and they can unselect them if  they decide they don't need them that way they   know they can see all because if they if email's  not selected they might not see the email feature   so i'd like to have them all selected so that  they can see them then they can unselect them if   they choose great so that's it for that now what  about the save update now remember i mentioned   to you before that when we're saving an invoice  especially one that's tied to a template like this   one is remember there's an invoice template here  five six and seven right if we've decided to add a   template to that i want to make sure that we save  all those details in a separate database so i'm   put using a separate macro to do just that that  separate macro that macro comes directly after   we save it so when we here's the invoice macros  here's our invoice save this is when we save the   main invoice when we come down here what i want to  do is i want to check let's could let's put this   possibly save recurring template and then we're  just going to put optional okay so optional   when do we want to save that well if b true is  true meaning the recurring is set if recurring is   turned on and i want to make sure it hasn't been  set before if b1 equals empty or ba equals does   not equal empty then reoccurring saves update  what does that mean i'm going to go over this   and this for you if b 1 equals empty let's take a  look at that inside our cheat here on invoice okay   b1 of course b1 meaning there is no specific row  that like here's an example let's take a look   at this let's add a brand new invoice we'll add a  client here and we'll add a few items here now if   we decide we're not going to make this reoccurring  right we don't want to make this reoccurring   this is false there's nothing recurring to save  right however if b1 goes to true we are going to   make this invoice reoccurring we see that it's  recurring so if b2 equals true then we know we   need to save this as we need to save these details  as reoccurring as well so that's exactly what that   if b1 equals empty right or range b8 equals  empty so b2 is true that's important right we   need to have that if b1 equals empty what happens  when b1 is empty well b1 is empty means there's   no existing template that's associated with this  invoice right there's no template so we know we we   know we have to save it because reoccurring exists  but there's no template so that means it's going   to be a brand new template there's no template id  that's associated with there's no template row it   does not exist inside this template database  and that means we have to save a brand new one   right here so when those conditions are met or  b8 does not equal empty b8 does not equal empty   then recurring save and update why would b8 not be  empty let's take a look in here if i decide to add   a let's say i'm going to load an existing one now  with invoice number five we know we were already   created now that five gets placed directly in here  we know that we now we maybe we want to save and   update right maybe we decide we want to make this  uh create the invoice every 15 days before right   and i want to when i save that update i want  to make sure that that change that 15 day gets   changed gets also saved in this template database  here i want the days before notice invoice number   five the days before got changed to 15. so we want  to make sure that we either say update it right   update because it's been saved before or add a new  one if it hasn't so there's a few different ways   the same macro is going to handle again  saving this information for the first time or   updating the information is that same macro so  there's several conditions when i want to run   this reoccurring save update several conditions  but always b2 it always has to be active okay   so does this macro this that we're going to  go with everything else here this is simply   saving the invoice just as we do each time and  we've done that on a few different ones again i   have a dedicated video on that which you can look  at okay so taking a look at this so reoccurring   save update this is the one macro we're going  to focus on and of course i want to make sure   if b4 equals empty then please save it has to  be an existing invoice i want to make sure that   we've already saved the invoice this invoice  here but of course almost always it should be   because we're only running this macro here after  we save the initial invoice so b4 would almost   always have a value b4 is the database row  that's associated with this invoice number   here five right the database row here that's on  our original invoice list right here so we know   that invoice number five is row number seven so  that's important we want to make sure b4 is not   we want to make sure to let the user know to make  sure so we're just going to run a check through b4   all right continuing on also if b1 is empty i  need to know has this template been saved or not   b1 this would this lets me know it's been saved  before yes it's been saved before and it's going   to be found on row 4. however if it's a brand new  invoice then we know and we do decide to make it   reoccurring we know that there's no row associated  with b1 is empty there's no row this is empty b8   and that means we would need to save it to a  brand new row here so either it's an existing   row as it was here or brand new row b1 will tell  us whether we need to do that or not okay we'll   load up five again just so we can see the details  of an existing one okay so continuing on if it's   brand new empty then what we need to do is we need  a brand new template row and that's going to be   found based on our invoice template database  a in the first available row and there's our   template row now our invoice template database the  first thing what i want to do is i want to assign   that invoice id to column a and then the last  thing i want to do here just for the do ones   in column s i want to put that row that row is  going to be very important because when i run it   through an advanced filter i need to update this  information as we create the emails i need that   so i need to have that row created up here right  here and in fact didn't get created i'm going to   check it out right now but i want that row it's  on u make sure it's on you let's put that on   u u u u not f u okay so u is going to take  on that column we need to put that on there   i want to make sure that it's very important not  a nest but on you okay so you will take on that   row number so we need to have that also  what if it's an existing if it's an existing   all i need to do this has already been added  if it's new if it's existing all i need to   do is take that template row and put it into a  variable and it's going to come directly from b1   everything else in this macro is going to be  regardless of it is in a new let's put this new   template and we already have the existing note  here okay so everything else we're going to run   of course a data mapping on that and data mapping  again if you've seen my videos before basically   all we're doing is it's a lot easier all i need to  do is map these individual notice b2 is true right   e4 is every quantity so all we're doing is simply  matching them here and there's a lot of so e4 is   going to take on that quantity so all we've done  is map the individual cells with that so all i   need to do is run a loop from two one's already  here all i need to do is run a loop all the way   from here all the way to the last column here  and that last column we already have here so   we're going to run it equal to column 20. so  running a loop and what i'm going to do is   simply take whatever's in b17 on the invoice and  i'm going to place it in our in this case b17 is   the created on date and i'm going to place it  directly inside that column the create on date   i'm going to place it directly on here on the  associated row that's all i need to do with data   mapping so 2 to 20 is where we're going to go with  that for the template columns equal to 2 to 20.   inside the invoice template database what we're  focused on and i want to do cells that template   row that type of column that value is going to  take on whatever's in that cell that cell can   be found on row one right here that's cell e4  b2 b17 whatever it is we're going to take that   value from our invoice remember we're focused  on our invoice screen range invoice dot range   whatever is in that cell and we're going to  save that update also again what i want to do   is i want to wrap that text as false why is that  important because some of these like the messages   contain a lot of text right and what's going to  happen is if i enter that it's going to grow that   right i don't want to see that that way i don't  want to look like this notice that that i just hit   enter so this is what i want to avoid something  like that so when you do wrap text through vba it   automatically hides it so wrap text equals false  so one more time here it's going to make sure so   doing that in vba make sure that that row width  does not expand so all we need to do is take on   that row after we've entered all that text and do  wrap text equals false and that's going to show   that that issue doesn't have them next up what i  want to do is i want to set that invoice number   for the template remember i said we want to make  sure that we know once we've created a template i   want to take whatever invoice numbers here saved  it and i want to make sure that that becomes the   template id here so inside b8 that's going to  ensure that row automatically changes right   so that row so now we know we have that so we've  just set up that's it that's all we need to do   to save or update the template okay that's it  that saves up to the database whether it's a new   one or an existing one so what about when we want  to load the reoccurring and just as with the save   i've decided to separate loading the reoccurring  details as opposed to loading the invoice they're   now separate so if we take a look on the invoice  here the original invoice and we have the load   which is down here load right we only want to load  it if it contains a value so here on the change   let's go to invoice here so when i make a change  to that when we're searching for that then we're   going to load it that's based on n2 so the first  thing we do is load the invoice run the macro here   and when there's a change to enter then what  we're going to do is run load that template   check for reoccurring templates so we're  going to run that macro so when we change   that n2 let's take a quick look inside here when i  make a change to that five or six the first thing   we're going to do is run the macro that loads the  invoice the second thing we're going to do is if   run the macro that loads the template but of  course we need to run a check to make sure   that we actually have a template that's associated  and how do we know that well we can do that inside   here the first thing let's go ahead and go back  into the recurring macros and there the first   thing if b1 value equals empty then exit the sub  remember b1 is our row that's associated with that   template if that is empty then there's nothing we  need to do so we can exit out so if it's not empty   it's very very simple we're going to assign that  as our template row then what we do is just need   to run a call run loop from 2 to 18. now why is it  only going to 18 because i i have some formulas in   here that we don't want to update so if we take a  look inside our template we're only going to run   it to this i'm not running i'm running it to up  to here this is 18 but i'm not going to include   it for this one and this one because our next  invoice date and our created on date we don't want   to load that up why because those are calculated  fields so our next invoice date which is here   and our created on date these are both formulas  notice that our last invoice date is not a formula   so i don't want to replace what's in b16 i don't  want to replace what's in b17 those are formulas   those are calculated so that's why we don't run  it and it's good if you have formulas if you have   put them at the end so you can run your loop  that's why these two contain formulas that's why   that's why i didn't include them so we're saying  b16 and b17 contain formulas so we don't want to   load this value inside because that value is going  to be calculated okay so that's why we're going to   run our loop from 280 and again all we're doing  here is just bringing it inside the invoice from   here we're taking that the range whatever's in the  invoice template row one in that template whatever   cell is here or whatever there is we're gonna  take that value is gonna take on that cell is   gonna take on whatever value is located inside the  temple row and temp call we're simply reversing   what we did inside this loop here simply a reverse  of this loop when we're placing the values inside   the database here we're extracting them from the  database and placing them directly in the forms   in our invoice sheet so the last macro that we're  going to focus on before we focus on the formulas   it's called reoccurring check and create now this  macro is the one that you want to run all the time   this is the macro that you want to run when you  open your workbook right if you're having work   you want to run this now of course i've commented  out because when you get this i don't want that   macro to run but this one you'll comment out  and it will check for any invoices that need   to be created and will automatically recreate them  based on the template database once you take this   comment out here it'll work automatically okay  so and also i'm going to show you a way how to   open the workbook automatically even while you  don't have to do it manually so we'll show that   to you at least in windows okay so we have that  here so let's get into that macro and take a quick   look at and see exactly how that's going to work  it's not that long of macro it's relatively easy   first thing what i want to do is i want to focus  i want to run an advanced filter i want to know   take a look at all these i want to know the  next invoice date the next invoice date or   more importantly the created on day when is it  supposed to be created if this created on date   is less than or equal to the current date then  i need to know to do it right so notice this one   this one here that created on date is october 30th  well of course and so this one this one's november   26th we know and this one's today's date so we  can create all three of these need to be created   because the created on date here is going to be  set so we need to know when are we actually going   to create these so it is that date that i want to  run through an advanced filter i also want to make   sure that the reoccurring is true if we decide  if somebody decides to turn it off we certainly   need to update that right up here it's going  to go to false so we want to make sure that we   update that ingly and so here recurring is  true and the created on date is less than or equal   to the current date which is less than or equal  to today so that's our criteria what i want to   do is i want to get those results and i want those  results to come right in here once it is for each   one of these i'm going to run a loop i'm going to  check to see if there's any results if there are   that means the result row is greater than or equal  to three i'm going to loop through all the results   and then for every single one i'm going to create  an email or i'm going to print it based on all   these settings if we're email if we're saving  it or for printing it and then of course if it's   going to if we're saving it i'm going to save  it to this folder i'm going to create a name   based on this name here and if it's an email i'm  going to create an email based on this subject a   message based on this we're going to set whether  we're going to display it or show it and then what   we're going to be doing is we're going to bring  all this information and we're going to calculate   a brand new next invoice data brand new created  on date and then we're going to save that to the   template we're going to take the row here we know  what original row is associated we know what row   to make changes to that and we're going to make  those changes and we're going to then update the   last invoice and make those updates ing and  that's exactly what we're going to do inside the   macro so let's just go over that right now first  thing what we're going to do is determine the last   row of our invoice template database if that's  less than 3 that means we have no data so we can   exit this up we're going to run that advanced  filter that i spoke of starting in a3 going   all the way to you and making sure you use your  headers right the headers must be the same in both   your original data your criteria and your results  that criteria is w2 through x3 w2 through x3 is   our criteria and we want those results coming all  the way in from aa all the way through ao now this   is just a subset of the original data because  i don't need all the original data i don't need   to bring in this one why is that important  because i don't need to bring it because as   soon as i bring in this template as soon as i  load this template this invoice all that data   is going to come all the way in here so what i'm  going to do is i'm going to take that original   invoice number i'm going to bring it directly  inside here i'm going to play place it right in   here as soon as i do that it's going to load that  invoice then what i'm going to do is i'm going to   create a duplicate invoice based on everything  here and then i'm going to update the return   the next days so that's just what we're going to  do inside here okay so taking a look at this our   results are going to come from a go to aa through  ao so we're going to get all the results in   that i'm going to do is i'm going to check for  the last row based on column a a so that's what   we can do right in here the last results row is  equal to a on f if that last results row is less   than 3 then we're going to exit the sub nothing we  can do there's no data that's fine the first thing   what i want to do is i want to set that print area  we've got a pretty fixed print area in this case   but if it's based on h2 through l2 i just want to  address with that so our print area here sometimes   we have dynamic print areas using the last  row but this time it's a very fixed print area   all the way from h to all the way through  l32 so we're going to use the address of that   to create that print range next up i want to  set for the outlook just in case we're going to   be emailing i'm going to set that out gap to the  create alec outlook application this allocat has   already has an object been dimensioned as an  object right here the outlet gap and the analytic   mail are both objects so we're going to use  late binding in those light banning is preferred   so okay once we have that and we create that  outlook application that's going to ensure that   you have that then what else do we need to do  next okay now i'm ready to run my loop i don't   want to create this inside a loop for the results  row equals 3 to the last results row i want to   know that template database row that's going to  be located on ao here i want to put that into a   variable because we're going to need that and also  what i want to do is i want to load that invoice   remember i told you i'm going to take whatever's  here and i'll bring it directly inside here   automatically that's going to load the invoice so  that's what we're doing n2 is going to be equal to   aaa and the results are set invoice2 this will  automatically load the invoice remember as soon   as i make that change it will automatically load  then what i want to do is i want to take whatever   is in l2 which is going to be our invoice  number and i want to place that directly in   b6 why do i put it in b6 that is going to be our  new invoice number l2 is going to take on remember   i want to do a brand new invoice l6 is our next  invoice number we're creating a brand new invoice   and i'm going to place that number directly  inside l2 here that's our brand new invoice number   so once we have our new invoice number we've  loaded up our old one right here then we're going   to take our new one we're placing it directly in  there then what i want to do very very important   is i want to clear the associated database  rows these database rows are associated with   a specific invoice if we take a look at row 12  and row 13 inside our invoice items here we look   at row 12 and 13 we see that it's been assigned  to invoice id six but what i do want is going to   be a brand new invoice id so i want to remove the  associations as soon as vba looks here they say oh   look there's no database row associated to that we  better create a brand new row that's exactly what   i want so i want to make sure to clear out the  existing database rows here because i want it i   want a brand new invoice to be created but i want  everything else to be the same except i want brand   new database rows to be associated with just as  we did here look these are exactly the same right   it duplicated what was here except they added  brand new rows here the same items the same prices   everything is the same except a brand new invoice  now they're associated with invoice number seven   so we need to do that directly inside the code  so also what i want to do is l3 i want to set the   next invoice date now where is that next invoice  date coming right here our invoice date that's not   the created on date the date the created date is  when that application is actually going to create   that invoice however the invoice date is different  right you may want invoice on a specific date   that invoice date is going to come directly from  here inside am 3 am or in the row associated   that's our invoice date so we're going to set  that invoice number date based on what is in   am in the result row okay then what i want  to do is i want to save it i want to run the   macro it's going to save it now this is going to  automatically save it as a new invoice how do we   know that because as soon as i add let's say  our next invoice number is going to be 8 right   as soon as i add that 8 here just if i were to do  it manually here what's that going to do that's   going to clear the row there's no row that's  been associated with that it's going to go blank   and as soon as this is blank it's going to know  this is a brand new invoices as soon as these   are blank here it's going to know that we have  have no database associated with it's going to   save it as a new one everything else is the same  except it's going to save it as a new invoice   so that's why we want to make sure that we run  that macro to save it after this it's been saved   as a new invoice now what we want to do is we want  to check are we going to be emailing it are we   going to be saving it are we going to be printing  it or what so let's focus on email and save   and the reason i want to focus on both of these  two is because for either one of these we need   to save a pdf if we're emailing it for certainly  we need to create a pdf to send to the customer   if we're saving it we need to also create  a pdf so either one of these options   and as soon as we've loaded let's take a look back  here let's load the original one as soon as we've   loaded we know that we're going to be emailed  this template we're going to be emailing it and   we're going to be saving it so we know b9 and b10  are going to be true so if b9 is true or b10 is   true then we need to save save as pdf regardless  for the email or for the save or for both so the   first thing what i want to do is i want to set  that save folder now that saved folder we've set   unique saved folder based on what's located  right here in e14 it's also the same folder   that is located in our database as we loop through  of course it's going to be located in ah 3 here so   it's ah in the result row and we're going to add  a backslash onto that save folder that's going to   use our folder now we also want to know the file  name now that file name is based on two components   based on the save name 1 and save name 2. it's  a combination it is the same combination located   right here in here so we've given the user the  ability to have a customized save name so we're   going to combine those two strings into a single  string now keep in mind that this string contains   variables we don&#