How To Create A Full Payroll in Excel Pt.1 [Employee Manager Part 15]

hello this is Randy with Excel for freelancers  and thank you for joining me for part 15 of the   employee manager we're gonna focus on payroll  and that includes payroll details the ability   to select specific employees to add to a payroll  whether we're gonna filter by status type position   or salary and add those employees to the payroll  and then of course we're gonna be able to review   those employees looking at each employees schedule  as far as the deductions additions and of course   adding and saving to those employees and moving  on to the next employee and we also have a summary   and create where we'll be able to have a summary  of all the payroll and total and that of course   we're gonna confirm and create that payroll we've  got that to show you plus a lot more so let's get   started already thanks for joining me today on  this create behavioral training effect we're   gonna cover over perhaps 1 2 or 3 parts because  it's gonna be a big training and we still have   got a lot to do on the summary we still need  to do that we have on the review we still have   some year-to-date to program and some formulas  but we're gonna get started and well we've got   a lot to cover but before we get started I just  wanted to let you know about our advanced reports   dashboard and master classrooms we have good on  which is it this application here which is a full   15 hour training you'll have the ability to learn  how to sort on click you'll also have the ability   to automatically filter based on partial selection  we've got amazing features in this application   including drill down and drill up at multiple  financial reports which we have on a profit and   loss we also have a pop-up picture feature we've  gotten the ability to toggle specific columns on   single click we have custom reports here which you  can add your own reports customize it we also have   of course save on PDF Excel picture as well as  email on so it's a ton of features include a link   down there if you want to take a look at that all  of our weekly training is free and to help support   that we do have these courses for you available  so I made sure to get you a link alright let's   get started on the training I've got so much  to show you in this payroll there's a lot of   features that we haven't covered yet so we want to  make sure to cover those in fact in the employee   we're gonna start off in the employee manager back  into that sheet because there are some features   that I did add there and the employee manager  previously we had our payroll details but our   paid deduction z' and our pay additions we're not  working in other words any changes that we did   were not saved and any changes we're not saved  into a database so we added some features there   in fact a feature that requires a user to add in  a frequency so we've used conditional formatting   I'm going to show you how that was done as well  so now we have the ability to save these so when   user makes the change they're automatically saved  and then of course when we reload those employees   those changes are automatically refilled in here  so we've got that going on as well same thing for   paid deductions any changes that are made here  automatically saved to the database and also the   conditional formatting we also have the same for  taxes too so we've got that as well and taxes here   we've added in the ability to add taxes and  then that information is also saved here so   it's a great feature so when we change employees  it's always cleared out but when we go back to an   existing employee that information is safe and we  have a brand new table and database that I'm going   to show you how we do that so we're gonna start  them up because that's an integral part of the   payroll these pay deductions pay additions and tax  information all three of them are used within the   payroll because when we do bring up the payroll  in let's go ahead and click on that when we're   are reviewing specific employees we want that  information to show up here these employees don't   have any let's pull ok this employee does so we  want those default taxes to show up here we want   those other payroll deductions to show up here  when we're reviewing a specific employee we want   to make sure that they're always here so we have  that ability and so we need to bring that up so   when we're reviewing a specific employee we can we  can do that so that's really important so we want   to get started on the employee manager first and  show you how we created those three things and if   you'll notice a few things you'll see these light  blue numbers here and here and of course those are   gonna be hidden once this program is completed and  they'll be heated using the same font color just   like we do but these are help to guide us these  are gonna guide us as to how to map the database   and that's something we've gone over in many  lessons before but it's so important we'll go over   just once more on this training as well on how we  map those to the database and speaking of database   let's go ahead and take a look at a new sheet we  have here if we scroll over to the right here we   will see we've got a new sheet called employee pay  default item and what this default item is going   to store those three items that we just talked  about those include tax deductions those include   paid deductions and pay Edition so all each time  we make those changes in fact that change that we   just made the health insurance here 250 bi-weekly  that is saved here so that's that's important   part so that's how we can recall it and so what  this item this particular sheet is made up of an   employee ID we need to track the employee ID so  we need to know we need to know what type we need   to know what table to stored it in is it going to  be a tax deduction is it going to be a paid dish   Edition or paid deduction remember we have three  different tables that we're gonna be storing in in   the employee manager here in fact let me bring  that sheet over I'm gonna move that sheet over   since we're focused on that all the way over here  so we can quickly switch between those these two   here okay let's good move now we have employee  manager so we've got three different tables here   we've got tax federal local tax deductions table  we have paid deductions these are for any type of   paid deductions other pay deductions that might  be paying on on a specific paycheck we have those   of you and we have also pay addition some things  you might want to reimburse your employee for so   we've got those here so we've got three different  tables but we can see they're very very similar so   when we have such similar databases in table we  can combine them into a single database as long   as we separate them going in and we separate them  going out so we done just that so for all three   of those those are all included here because we  have separated by item type so I've created a   column called item type and that's going to help  decide which table they get stored in when they're   brought back into the employee manager all right  so we've got item name we've got the name of the   item each of those have we also have the tax items  have both of these the tax item just the tax have   wage percentage of base limit so I've created a  specific column for those you'll notice that pay   additions and deductions don't have those items  but tax deduction items do we also have the amount   and the frequency these are going to be used for  pay additions and paid deductions and I've got an   employee manager Oh what is this well when I make  a change when I make a change I want to make sure   when we bring that information back into it I  wanted it on the same roll I don't want these   to be restored I want them to be in the exact  order that they were initially put in so if we   put in a health insurance or tools payment we've  already got those and if we put that information   in I want it when we refresh the employee I want  to Brok back in on the third row exactly on row   110 I want that brought right back into that row  so that when we change employees and it's cleared   out and I bring back that employee and refresh  that employee I want it on the same exact row that   we just created I want that right here back on row  110 so the best way to do that is to save this row   number 110 so we can do just that in our employer  so that way we save 110 when I'm gonna put it   right here so when I bring it back I know exactly  what row it is and we have another row and this is   row 16 this is the database so formula so that way  for example if we need to change this I know what   row it's changed so for example if I change this  to 130 I need to know what row in the database to   make that change that rows right here so I know  automatically row 16 I know the column change   it to 130 so when we go back in you're gonna see  it's automatically been changed to 130 and that's   how mapping helps we know the row we know that of  course the amounts are always in F so as long as   we know the call and we know the row we're gonna  be able to automatically update that and so that's   really really helpful and one of the things moving  forward is when we have a new item well what about   when we have a new item there is no Gro associated  with that right well in that case we know if there   is no row associated then use the first available  row what's gonna be the first available row in   this case it's gonna be seventeen so when we add a  new we know in VBA to make sure this is seventeen   so as soon as we add a new one it's gonna change  the seven that's gonna create seventeen and now we   know and now if you look here it's a new row so we  go back into the database well know automatically   seventeen now has a pay deduction health insurance  so it's got all of the required we don't need to   add this information again it's all here all we  need to do is to make any changes here so it could   be the wage or base limit if it's a tax item or  it's gonna be the amount of frequency if it's so   when we go ahead and back into that and we change  it to 25 the amount and we put in a frequency   there let's say monthly that automatically is  going to update we know the row 17 already so   now it's just a simple update right here so we  know 25 and monthly it's automatically be added   alright so let's go so what I want to do is I want  to make sure that if the user enters an amount   here I want to make sure that we tell them hey  we need a frequency to the amounts not sufficient   enough I need to know how often and that's gonna  be hold true and pay deductions or pay additions   so we can use conditional formatting let's go  ahead and take a look at that just highlight   that go into the home and conditional formatting  and manage rules let's take a look at the rules   there's just two rules one of those rules is gonna  highlight the odd row or the even row in this case   we're using the mod we've seen that before and  the other conditional formatting is brand new   and those are gonna affect I 128 through I 139  and I want to wade through I 119 those are both   for the paid deductions and the pay additions  because both of those rules will apply to both   of those tables and the rule is simple there's  two conditions what are the two conditions one   let's go ahead and close this out for a second  one we need an amount here that's one condition   this needs to be blink so there's two conditions  this contains a value and this contains blank when   that happens color this color this red so we can  do that back into that and I'll show you how that   rule we can use an and because there's multiple  conditions multiple conditions so we use aunt   and we'll go ahead and take a look at that let's  bring that here so we can see both edit the rule   we're gonna look at the end so the and is h108  does not equal blank right H is this column does   not equal plank and i 108 does equal blank now the  important thing there's two important to get this   to work that i see a lot of people making mistakes  on in fact i've made those mistakes pretty often   too and the critical thing is 1 you do not want an  absolute dollar sign here before went away because   this needs to hold true for the whole table so  the first rule is make sure it is not absolute   zero does not absolute meaning there's no dollar  sign there on both of those so that's rule number   one what does rule number two rule number two is  our table must start in 108 must start at the same   place the rules are 108 or in this case 128 but  so as long as those rules apply it starts there   then we are good to go so that is how we do that  so that's gonna help us so those are the second   rules of the table must start wherever your rule  starts the first the first in this case it's 108   so 108 must be our first on the table and that of  course is always going to be absolute so those two   rules when we have those two rules it's always  going to work properly and it's gonna work just   for the row that it has the issue and that way  as soon as both of those rules are false or one   of those then automatically it's gonna go back to  the colors so that's gonna really really help us   and that's how we instruct a user to make sure  to fill in a required field all right so we've   covered that we've covered pages since let's go  into the VBA and see before we get to the payroll   let's go to the VBA and see how we did that one  how there's really three three things going on   here one how do we save changes automatically so  that those changes are automatically put into the   database to how do we load how do we load it's  getting me let's go back in to cancel how do we   load automatically those payroll details when  it comes to payroll deductions how do we load   that information and three how do we save it if  it's new if it's a new transaction how do we add   the row and save it so let's go ahead and focus  on that into the VBA into the developers if you   don't have the developers tab of course you want  to go into the options here and make sure you're   gonna look under the customize ribbon and select a  developers ribbon alt f11 will also get you there   so you can use that as a shortcut into the VBA and  we've got some a lot of code here on the employee   manager we're gonna focus on this for right now  and then we're gonna go into that so the changes   are we're gonna be changes right changes we're  looking for changes not selections changes so   let's go ahead and take a look at this on change  of employee federal and local and tax deductions   right and we got on change of employee edition  so these are the two sections that we're gonna   focus on right now right so what do we do so the  first one is the on employee federal and local tax   deductions on change but not on employee load why  why do we say not on employee load let's go over   that when we load an employee if I double click  on that it's gonna refresh and those are changes   right these are changes to this cell because  it's going from cells with values to blank and   then back and then back to values again right so  that's a change but on that type of change I don't   want to save to our database so don't want to save  anything to this on that type of change now how do   we differentiate that type of change with a user  actually putting in a specific change that's a   change too so I need to differentiate between  those two well we know any from prior lessons   that we have something called employee load and  we look into b1 let's go ahead and unhide this B   I believe it's b1 it's gonna tell us whether its  employees loading or not when the employee loads   this goes to true when it finishes loading it goes  to back to false so we can say if there's a change   and this is true don't do anything don't save  those changes to the database don't do anything   because loading right that type of change we need  so we want to make sure always that that the type   of change when we're loading so when this is true  don't make any change I don't think you can see   it a little bit too quick oh yeah you can see it  there we go so when we load an employee you'll see   how this goes to true and then back to false so  when that happens that type of change we do not   want to save it so when we do that we can just  going to instruct VBA not on B one if only it B   1 equals false so we've done that in the code so  we're gonna say if there's a change to G 93 to 104   that's the that is the tax table and B 1 equals  false so those two conditions B 1 must equal false   so that's how we differentiate between so in those  two cases if the user makes a change to anywhere   any cell between gene 93 and I 104 and B 1 is  false then do something what we're gonna do we're   gonna do all of this first we're gonna dimension  the payroll item row as long I need to know that   row that rows gonna need to be new right or it's  going to be existing and how do we know whether   it's near existing well we know we just gonna look  in column K and if if there's if we're gonna be   into a new item and there's nothing in column K we  know that it's gonna be a new payroll item however   if there's a value here then we know that it's  going to be an existing item and remember when   we select a new employee this information gets  cleared out you see there's nothing here so we   want to make sure we've added clear these cells  out so each new employee gets automatically that's   how we differentiate so we make sure to clear that  and we clear this here as well these are formulas   so we don't clear those out so we just clear these  cells all right so back into our employee so that   is how we go ahead and clear that buttons a little  bit close you see how that is that's probably too   close so when you when when me as a user I use  you you're gonna make those mistakes this is   too close so we have to probably increase this  row or just move these buttons over a little bit   but those kind of errors those kind of usage  issues are really important so when you come   across those let me know you know because we all  make those mistakes so that kind of thing maybe   we'll get that fixed up on the final we've got  a lot of bucks but this is just too close users   can easily click the add new so all right just  going over there so when you find yourself making   mistakes like okay if you make those mistakes  your users are gonna make those mistakes too so   keep that in mind just as I do alright so we're  focused on this 19 so if there's we know so we   can use those we can use this whether there's a  value or not to know whether it's a new item or   not if it's a new item we're gonna find the first  available row we're gonna use and LX gonna find   that then we're gonna add the details here now  for a new item we want to make sure we add the   employee ID we want to add the tax item type we  want to add the item name we want them sure to   add the employee row in the database row those are  four new items for existing items we just need to   update the details so there's a difference new  items we get a lot of more data existing items   just whatever the update information here we don't  need to add the row again we don't need to add the   database we don't need to add the type or the  employee ID so that's not gonna change so we do   those only on new items so let's go ahead and back  into the employee manager and then back into the   VBA and show you that so the first thing we want  to do is ask an if statement if K and the target   Row is empty then we know it's a new item and if  it's a new item we need to add a lot more things   right if it's an existing item we just need to  use them our data mapping and have one line of   code so new items we do a lot so what do we do on  the new item well the first thing is we need to   know the pay item row we need to set that up pay  item roads gonna be sheet 17 sheet 17 of course   is our is our employee default this is sheet 17  right here employee default payroll items that is   our sheet 17 so with that sheet what we're gonna  do is we're gonna find the first available row we   can use and Excel up to find that plus one this  is gonna find us our last row of data with data   this plus one it's gonna add one so it's gonna be  our first available row that's gonna set the pay   I to row now that we have that now we're ready to  add in the data we're gonna know a it's gonna be   where employee ID more employee ID is found in  j2 on our employee manager and you know there's   there's no sheet reference here why is that  because we're on the sheet we're all an employee   managers sheet when we're on a specific sheet we  don't need to refer to a specific is already known   that when you use it now if we do need to refer  to a specific sheet we do need to put that name   in if we need to refer to other sheet other than  employee manager we need to specify it however if   we're on the sheet we don't need to specify sheet  one we do not need or employee manager we'll just   assume because we are coding on the sheet itself  now of course we're for an individual module we   always need to specify the Sheikh we don't know  all right so we've got that employee ID and then   of course we need to list in the column B we need  to know what type of item since we're focused only   on the employee federal and local tax deductions  we're gonna mark this as tax deduction that's   going to be the item type that we're gonna set  now of course in H remember H we need to save our   employee row what bro what row are we working on  here we need to know that and I need to put that   in column H right here so whatever row were our  target roads get to go right here and then of   course we're gonna put in a formula row and that's  gonna help us once we filter it I'll show you how   that plays in a little bit later on but once we  run our advanced filter in there we need to know   what row to go back to this is gonna help us if  we're as a change so we put this row and we put it   right in here and then if we make any changes we  know exactly what road to make those changes and   so really saves us a lot of code alright back  into the VBA so we're gonna put our database   manager own column H we're gonna need to know that  if we make changes and of course we're gonna put   our in our formula row which sets our database row  up and of course one more thing we need to do is   we need to mark K call it K with the pay item row  and that means I've got to put that row right here   in column K so when as soon as we add a new item  that row is gonna be placed right here and moving   forward and when we make changes to either these  two columns we automatically know what row to put   that in so that's how we've got that so that's all  for a new item this is all for a new item what if   it's an existing both existing we use data mapping  the pay item row else paid row equals K in the   target row if it's an existing we have we can set  the pay item road to right here we know if it's   existing our padam rows gonna be in case so we can  set the patter right pay item row right there all   right moving forward now we need to the last item  and a regardless whether it's new or whether it's   existing right we're new or existing but for both  conditions new or existing we are going to use   data mapping and what we're going to do is with  that mapping is we're gonna save anything else   to the database and remember we have some other  things what if we make a change here here or here   I want to save those to the database and how do we  go about doing that well actually I have my data   mapping here and pay additions and that's gonna be  located right here three six and seven this call   this row 140 is our data because I've put in I put  in the number three I've put in the number six and   number seven why did I put those numbers in these  are the columns in which we're going to save it   in our database so I know if the user makes a  change here to change column three right here   column three is C right and then also column six  and seven six and seven right so this is called so   if you look here equals column right that's gonna  be column three and if we drag this over we're   gonna see that we also these are these two are  gonna be used for tax item right so we know that   but we're focused on oh we were focusing on tax  so let me go back to that six and seven so based   on tax let's go pull that tax up again so we can  because we were on that so let's I want to make   sure we cover that back in there employee manager  so we're on tax information and here we have three   four five and six we have that on the tax so these  are the column numbers three is the name or that's   the same name regardless of it's a tax item if  it's a deduction or addition we're gonna go call   them three four is the wage percentage five is  the base limit six we don't need that's gonna   be calculated so let's get rid of that we don't  need that pay deductions and pay additions have   the same three six and seven right so we we've  mapped those columns and they're the same here   3/4 is the wage 5 is the base limit amount is 6 7  is frequency 8 we don't need ok so that's how we   map them so that means so let's go ahead and take  a look back in there when we make a change into   the employee manager when we're in tax information  when I make a change to a tax information or pay   deductions for example here's and let's go to  pay Editions this is Li because we've got the   mapping right here okay so if I make a change here  let's say MIT let's make a change here because we   already have a road we know what row and we  know what column row and column we need those   two things row and column to make those changes  so if I change this to I think we have this open   if I change this to anything right I know I'm we  know the sheet because it's gonna be employee pay   defaults we know the column column 3 and we know  the row so when we make a change to row 8 column   3 we know it's automatic so we know where to save  that so that's how mapping comes in handy that's   why it's so important we know it that way we can  just use one line of code now let's do it again   if this is column 7 and it's row 8 so any change  we make here column 7 row grow 21 in this case we   know that it's gonna automatically make those  changes in our data in our table right here so   we go to row 21 we're going to see it's now paid  weekly right I've got the columns here which we   don't need so it's now weekly so automatically it  gets added here so if we continue on and we add an   amount we add an item here so food reimbursement  and we make this 100 now we're gonna see weekly   it's gonna be all set up we know the row and we  use this these columns here so we know they're in   rough 40 so we're off 140 and the target column  that's very important that we're gonna pull the   3 we're gonna pull the 6 I'm gonna pull the 7 so  into the code let me show you how we did that so   in this case it's going to be 90 why 90 because  our tax information which is what we're focused   on is on Road our columns I've put here in row 90  so 90 is gonna tell us two three four or five so   back into our data mapping cells we know the row  right we've already defined the row if it's in   it's gonna be in K newer regardless now we've  defined the raw ready now we need to get the   column well where's the column for our tax for our  tax items our column is in row 90 of the current   column row 90 of the current call this value right  here this value is gonna tell us our column three   six four or five right three four five so we know  the row we know the column now we could say sheet   17 of the current of the row and the column equals  a target value so this one line of code will take   care of all of that and that's how we do for the  tax items for the additions and deductions it's   exactly the same exactly the same we define our  range in fact we've got two range so we're gonna   using both additions and deductions that's why  you have two ranges 108 to 119 and 128 to 139 that   covers both additions and deductions pay additions  are here our ranges here which is G 108 through I   119 pay additions it's gonna cover this rage right  here G 128 through I 139 so we've covered if the   user makes any changes in either one of those two  ranges and and of course if the employee is not   loading meaning b1 is false then do something all  right and what are we gonna do and b1 equals false   then we're gonna do all this again we're gonna go  through the same if the K and the target row the K   is empty that it's a new pay item otherwise it is  an existing pay item if K is a value we're gonna   pull it from K so again we're gonna do the same  things we're gonna we're gonna determine the pay   item row if its new based on the first available  we're gonna set the employee ID in column a and   then in this case we're gonna do is something  a little bit different I need to set whether   it's a pay addition or pay deduction right we're  gonna use this table for both pay a dish or pay   deduction right here pay deduction we don't know  because we're using this table throw so how can we   differentiate how do we know whether it's a paid  addition or pay deduction well let's take a look   here if it's a pay addition it's a pay deduction  our row is going to be somewhere between 108 and   119 if it's a paid addition our row is gonna be  somewhere between 128 and 139 so we can use that   row differentiation to determine whether we are  working with a pay addition or pay deduction and   that is exactly what we did here in the code if  the target rose greater than 127 greater then we   know it is a pay addition that we want to set B  and the payroll to pay Edition otherwise set it   to pay deduction otherwise set it to pay deduction  right pay deduction is first right so pay addition   is second so if it's greater than 127 right here  pay additions if it's great 127 here so if it's   greater than 127 we know it's pay addition if it's  less than that of course it's a paid deduction   because when 19 is our last we're also we can use  that target row to determine whether the user has   made a change to either the payroll deduction or  payroll addition and then we can make that change   in the database correctly right here so of course  we would put in pay deduction or pay addition here   in column B so we can differentiate between those  two all right next up so we have that now we're   gonna source we're gonna set the employee manager  row and call them H just like we did in with the   tax item up here and of course K and the pay item  rail we're gonna set that right here and of course   we're going to use a target row we're going to  set the target row here this time it's 140 140   is where we're gonna determine our columns and  we just set that up here in the employee manager   right here under the pay editions 140 is the row  that I've used to map our columns whether it's   call them three column six or column seven and  of course we're going to change this font color   to the same as the background color so it's going  to be gone when we do final version these will not   be visible so using these 100 140 and the target  column we know the column of the database whether   it's call them three whether it's call them six or  call them seven so we can use that and that's just   gonna map our data just like we did in the tax so  back into that we can use sheet 17 the row plus   the 140 and the target column this value right  here this one right here that's our column number   this is our width is just three six five or six  or three six or seven I should say three six or   seven so we know the wrong with 367 that's gonna  we're gonna put the target value right there and   that is actually all we have to do to make those  changes so that automatically any changes that we   make are automatically saved so if we change this  to 200 in row 21 our amount automatically changes   to 200 right here alright so now we know how to  save those changes but how do we load when we load   an employee how do we clear this out and load that  information in well we can use advanced filter for   that we know the employee ID is here and we also  know that we have the employee ID here so all we   need to do and we know we have the employee ID  here so all we need to do is run an advanced   filter for just this employee ID and we can do  that right here so we run our advanced filter we   create a criteria for our advanced filter right  here and we put in the criteria right there so   we can do that here if the employee IDs here and  then we run our results here so all we need to do   is create an advanced filter put in our criteria  which is twelve twenty and then we run a filter   so we pull only those items I had tax table items  right with or deductions or additions with that   specific employee ID then what we do is we run  a four next I determine the last row we make   sure that there is data so if the last row is less  than three then we know that's gonna be less than   two then we know there is no data so we do want to  check that in our code so as long as there is data   here we're going to run for next loop from row 2  all the way to the last row and what we're going   to do is we're going to determine if it's a tax  deduction we're gonna put this information we're   gonna put this in road 93 we're gonna put this in  row 94 I'm gonna put this in row 95 and what I'm   gonna do is I'm gonna put the name the wage of the  base limit and then I'm gonna put the data base   row and put that in a column Kihei and then of  course for pay additions we're going to add in of   course our name we're gonna put it in our amount  and monthly I'm gonna put that in row 128 and   we're gonna put our column and column K right so  we're going to put in I for pay Edition so we're   gonna put in the name the amount the frequency  and in column K we're gonna put the column the   row of the database right here so that's all we  need to do in the code you just need to run run   at our criteria run our advanced filter have all  the filtered data appear here run a four next loop   from the first to the last determine what type it  is and then where to put it what are we going to   put it here here and we're getting either if it's  a tax item we're gonna add this information if   it's a addition or deduction we're gonna add this  information and we're gonna add this information   so that's how it's done let's get to it let's show  you how we did that in the VBA so we've created a   module here actually in the employee we do have  employee missile employee load macros here so   we've added that in and of course any employee  load I've added pay AIIMS refreshes the brand   new macro you've seen the others before but I've  added employer item refresh and this is consistent   with the other macros and basically what I  want to do is I want to run a separate macro   that refreshes the payroll tax other deductions  and other additions into the employees and if   we scroll down we're gonna find that macro all the  way down here and do the employee pay item refresh   all right so we're gonna walk through this line of  code just as we did before so we need to determine   the last pay item row because if I'm gonna run an  advanced filter I need to know what the last row   of data is here in this case it's 21 I want to run  an advanced filter all those with all of the data   so I need to first get the last row so we need to  determine what that last row is alright neck stop   in our code we have the last filter Road we need  to get the last row of the filter that's important   we also need the pay item row we need to run  through all the rows so we're gonna run and I need   to know the employee manager row and the item type  so we're gonna all of those are gonna be defined   and with 17 we're gonna be primarily focused on  17 which is our pay item default tables just as   we've been focused on that same table that we're  focusing on the first thing I wanted is clear any   previous criteria and results I want to clear it  out I want to make sure that all this is cleared   out just like that I want to delete it and clear  it all out so we can get ready so I can put in the   employee ID and all the information so we want to  do that that's the first thing we want to do next   thing I want to do is m2 that is our criteria  I want to put the employee ID right in m2 the   employee ID it's gonna go right here at m2 because  that's our criteria how we're gonna and next up I   want to know the last pay item row that's the last  role in our main table we're gonna use called a X   so to run our advanced filter we need to know the  last row of our main data so next up what we can   do is we can say now we're ready for the advanced  filter so with cheat 17 of course a2 is our first   column and first row including the headers  remember when we run our advanced filter we   must always include the header row so a2 is gonna  be the first and the last is going to be a and of   course the last row of data that's gonna be our  main did in which we're going to be running our   advanced filter so it's going to go all the way  from a2 to I and the last payment pay Adam row and   we're gonna run our advanced filter we want to we  want to copy that information over and of course   we've got our criteria it's gonna include the  header here and the value here so m1 through m2 of   course that's the employee ID and what do we want  the results to appear we want the results to be   R in all the way from o1 to w1 that's where we've  set the area for our criteria and that's gonna be   just the headers oh 1 through W 1 and that's what  we want all the information to be displayed below   there so that's gonna get us and of course we  want the unique values is true now we've got our   data now when we run our advanced filter I just  need to perform a check I need to make sure that   there actually is data here sometimes employees  may not if they're new so we don't want to go   through transferring over running a for next loop  if there's no data so we need to check what is   the last row if it's less than two that cancel out  but if it's more than two of course continue on so   we're gonna run that the last filter row of course  equals o calmo and up that's getting us our last   filter row we're gonna run that check if the last  filter row is less than 2 then go to no payments   can skip all of this and go right here we don't  need to continue on we don't need to transfer   any information to the employee manager because  there is no data of course assuming there is data   meaning that there is values in 2 or greater then  we can run our for next loop that I discussed for   the pay item equals to 2 the last filter row and  the item type it's P the item type is P and calm   P and the item type row that is gonna be our item  type and calm the P here right P is gonna be our   item type item name is Q employee ideas here and  our employee manager row that's gonna be important   that's in column V and our database room calm so  keep those in mind when we run it the employee   manager rows in V right we just saw that and now  we're ready to go to sheet 1 that is our employee   manager sheet we need to did we need to say Chi 1  because we've we're using sheet 17 here so we need   to specify sheet one right here G right G equal a  Q and the pay Adam row this is our item name then   we know the employee manager row employee manager  Rose here and equals queued the name it's always   gonna go in G the name and that's regardless of  the item type regardless let's take a look at   that just so you know regardless of the name is  always gonna go in G's so let's look at that we   have three it will focus on three tax name also in  G pay deductions also in G pay additions also in   G's so that's constant for every item and another  thing constant is the rows always going to go in k   k here for pay additions k for pay deductions k  for the tax information so those are consistent   regardless of the table so we can add those  if we don't need to run an infinite we know   that G is gonna have the name which is in queue  we also know that K is gonna have the pay database   row so we can do those things regardless of the  other tip and now we get into the deductions or   additions now if the tax item equals tax deduction  then H and I equals R that's what is that H and I   H's wage is based a little bit so we're gonna add  that information equals what is it equal here wage   and base limit R and s Harz wage so we just need  to bring that information over into right here   wage and base limit so those two items are gonna  bring brought right in there so we've got those   covered but what if it's a pay addition so if it's  tax deduction then do that else pay additional   deduction else then H equals t which is the amount  or u which is the frequency so again if we're in   paid deductions or pay additions the amount is  in column H and the frequency is in column a and   that's the same for deductions and additions I and  H so we can bring what we want to do is I want to   bring the amount and frequency from t and u here I  don't want to bring them in too right here H and I   so we've done just that with those lines of code  H equals T the Mount here and the frequency here   we're gonna run that loop run this loop for every  single line that's gonna bring it in that's gonna   bring it in for whether its tax so it's very  relatively simple code for all three different   databases all three different tables so that's it  that's it that's all we need to do with that macro   and that's gonna blow it in our information we run  this every time an employee loads so that's how we   do that now we've got now we know how we save them  now we know how we load them and now we know how   we get this information in this and the reason  is we need to make sure that this information   appears on the payroll so let's move next on to  the payroll screen which is a great screen and   I'm glad we're gonna get that started today we're  going to continue it next time but there's a lot   I want to show you on the payroll and basically  the idea what the payroll is when we click on   the payroll details what I want to I want to give  the user the ability to select a specific payroll   let's move this over I want to give the user the  ability to select a specific payroll and have   those dates appeared and if you remember we have  a list of payroll dates right here you remember we   created these in the admin screen let's go  to review here in the admin screen we have   the ability to create payroll here's our payroll  periods and we have the ability remember when we   click create payroll we can set how often we want  our payroll we get a start date how many we want   to create and we can create those papers based on  a frequency and based on a start date and so once   those are created we then have the period name  we have a start date we have an end date so if we   select any one of these from a drop-down list we  can pull then both the start date and the end date   here so back into our payroll screen we go and  we'll show you just how we need so when I pull a   specific payroll I can then automate the specific  from date and I can automate the to date or we can   add in our custom date so I want to be able to set  the payroll dates that's very important so we can   set the from and to day because we need to know  we need to pull the time clock data from based   on this from and based on the to date I want  to pull the time clock data we have all of our   time clock data list here so I want to pull that  information based on the clock in data I need to   pull it based on just those days so that's very  important alright so moving back on to payroll   so we now we know why we select a paper because  that's gonna automate our from in two days I also   may not want to pay every single employee maybe  I only want to pay a certain active or inactive   or terminated employees and we can create multiple  statuses from the admin and then we maybe want to   pay only on status or maybe we only want to pay  certain types maybe we only want to pay hourly   employees or maybe we only want to pay salary  employees so we have the ability to display only   those employees that are marked by pay type and  of course in the employee manager in the specific   employees settings we can set the pay type let's  go back into we can set the pay type into general   info here we have oh sorry that's would be in  the payrolls and detail here pay type right here   hourly your salary so we can set the pay type here  all right so that's just a little bit refresher on   on pay type so we can differentiate we may not  want to pay all those employees at the same time   all we want to select specific we also have the  ability to if we want it we can create multiple   positions and then we can pay based on a position  maybe we only want to pay field staff or maybe we   only want to pay the owner that's a nice idea if  I'm the owner or manager right I don't really have   too much of this data set up but you can see  it gives you the basic it filters it based on   the position or maybe you have employees based  on different frequency maybe I only want to pay   weekly employees maybe I only want to pay those  on bi-weekly or on summer so our employees have   different paid frequency we may want to pay based  on just those frequencies so we have that ability   well so basically the idea is to filter your  employees if you develop a large company you may   have different payrolls for different employees so  we also have the ability to select all it's like   none and the idea is I want to select specific  employees I want to bring them over to the   employees maybe you want all but if we just want  some we can just select those employees and then   I want to add those selected to here I may want to  remove the selected which we can do here so it's a   really great way to do it I'm gonna show you just  how we did that so add selected and then remove   slick so we can get the way that that's a really  great feature I think we add I think it looks like   looks like I gotta add some I think it's not quite  there's a small bug here I'll fix that before it   gets sent to you see we've only selected three but  let's select none and see if that works all right   that works so there's a small bug in here when  you select multiple ones I think it added more   about fix that don't you worry about that and so  we also have the ability to select multiple and   then add this you see oh there we go now it's  working all right there's a small bug I'll fix   that no worries and I also want to summarize the  paper this is not working yet but I'll get it   working I want to know how many working days are  in the pay period based on the company I want to   know how many employees were selected this is just  a number it's not it's not formulated yet but I   just kind of put that as a placement here because  I don't want to remember that so let's see how we   did that in step 2 of course we're going to review  the employees and I want to show you that we'll go   through each and and of course step three we're  gonna have the summary of everything so let's   go through just this one and see how we did that  into the developer's tab we oh one more thing we   also want to display employees but with pay period  time clock data I may only want to display those   employees that have data in the specific time  period not all this is not functioning yet but   it's the idea I've placed here because I want to  make this function and what that means is I want   to remove all the employees from this list that  don't have any time clock data in this specific   period from or to in this specific period I only  want to display employees with data so that might   be really helpful so I've added that in but it's  not quite functioning yet I've had a lot of work   to do this week so we'll get to it of course  payroll is a mess so I want to go through it   slowly so you make sure you get everything you can  customize this or do your own of course alright so   into the developers tab let's take a look at this  under the visual basic and we're gonna focus on   the on sheet the payroll let's go ahead and click  on the payroll sheet and we've got some selection   changes here and we've got some worksheet change  here so let's go on to the selection change and go   over that the first thing is we notice we've got  some code we do have a pop-up calendar as you've   noticed here under our from in two days and that  gives user the ability to customize dates based on   this of course remember this calendar I created  so you can change the color and customize it to   any different color you want so it's really great  it's a shape based pop-up calendar so keep that in   mind and once you change it of course as always  change let's get back to that blue or a blue and   so we have two let's do code so that means j6 or  of course j8 if there's a selection change out of   those I want to show the calendar we can do that  here we can just say basically what this code does   is if the calendar is missing it's going to undo  the last action just in case it isn't missing and   that's gonna help undelete it if it if the user  accidentally deletes if that's what this code is   for here and then of course if the user makes a  selection change to j6 or J also a.m. for later   on in the payroll this will come up J AJ 6 2 J  if it's nothing then we're gonna check to make   sure there's a sheet that's part of the macros  to run the calendar and then of course we're   gonna show the calendar gonna run a macro that  shows the calendar otherwise hide it means if the   user selects anything else hide that calendar the  user selects anything else hide the calendar all   right so we've got that covered the reason didn't  work here so that made multiple selections that's   gonna work if we select one single cell it will  work all right so that's how that is done there   now we said on selection of employees to add on  selection if there's a if there's a selection   to d 11 through d3 10 or L 11 to L 310 I want  to make some changes what kind of changes will   kind of selection changes that means if I if the  user makes the selection change to this or usually   makes a selection change to this here L starting  it out eleven all the way to 310 and the same   thing with DD eleven I want something to happen so  the user gives the ability to add those selected   employees to there so we can select an employee  and then add those two employees so I want to   be able to select those and have them marked as  checkbox and if there's a checkbox I want to hunt   check them so let's check or uncheck we did go  over that in a training below before but I'll go   ahead and go over that to you so it's relatively  simple if the target value equals B what is this   B that's the checkbox and a wing-dings font  wing-dings font that's the font we're using so   if you take a look at this font here right and we  go into the home we'll see that that's wing-dings   font and of course you can find these characters  when we go ahead and insert and insert the symbol   here we could find that right here under the  wing-dings font and it's the check box that we   can use check box so we can use a few different  ones so that's how we did that and that of course   is the wing-dings font so the font for that cell  is automatically set to wingdings so all we need   to do is enter the character let's exit out of the  cell all we need to do is enter this character and   automatically show up as a checkbox if the target  value equals the check box then uncheck it this   character here's the unchecked box unchecked box  however else else here else put it as the check   box so we're checking for a check if there's a  check box we're gonna uncheck it else check it   that means if it's curly check-check it so that's  a here's it something if the target called equals   four then B seven equals false what is that well  that's the reason is let's say we check all here   but I want to check some I want to uncheck I want  this select all to automatically go to unchecked   and the same thing for this if I've selected  all and this is checked but if I select one I   want this to go to I want this to go to uncheck  automatically well this checkbox let's take a   look at this this is grouped let's take a look  at this checkbox right here right click and then   we're gonna format the control now if we look  at this checkbox will see that b7 is the cell   b7 as a cell so take a look at that if I mark  b7 is true it's gonna automatically be checked   true it's gonna either be checked if I mark b7  to false it's gonna automatically be a check so   I know that if I uncheck these I want to make  sure b7 is false if I check or uncheck I want   to make sure in this case b8 is false if we right  click this one it's in the group right click here   and then we'll go to format control will see that  this the cell link is b8 here so I know that if I   make b8 false or true I can change the status of  this checkbox automatically so we could do that   let's bring this up a little bit okay so we can do  that using the Falls of truth so that means when   I select it I want to make sure so if the column  is for if this column D is for then make b7 false   however if the columns not for it's going to be  one of these then in that case make b8 false so   that's all we've done with that one line of code  if the target column equals 4 then b7 equals false   else that means you know the target columns not  4 then b8 is false so we need I want to set those   two that's kind of a nice feature that gives the  ability the reason we do that is so that if the   users let's say we select all right and let's say  the user unselect too but he wants to select all   again all he needs to do to recheck this and it  selects all again so let's go ahead and go to   that see how did we assign let's take a look at  that and right click and see how we assign the   Macra to do that and as a macro called payroll  said to pay select all payroll select basis at   all so let's go ahead and look at that and find  that matter and see how we did that under we've   covered the selection change already and so we've  covered that so now we can move on to the macros   under the pay roll here payroll let's see we have  setup here and that's gonna cover I believe it   payroll remove selected boys select all this is  the one we're focused on payroll select to pay   select all we also have one more payroll employee  to pay select all so we have two of them to pay   is here right and then to be paid is here so  we have two different macros almost the same   functionality but they both are covering different  columns so we want to focus on that so the first   of which select to pay select all we're going to  determine the last legs rows long and you know   what the last row is and we can determine that  I want to know what the last row of employees in   this case of course it's 102 so I need to know  that in this case of course it is 19 so we need   to determine the last row so the last row in the  selected is d9 9x and up maybe we add one more   nine occasional over a thousand employees alright  so the last selected row is going to be d and let   that's gonna give us our last row now we know if  B 7 is true we need to know whether we're now this   has two functions right it's either gonna select  all or select none there's two different functions   so if it's currently if this is currently true  that means they're all selected then what I need   to unselect them all if it's currently false  if B seven is currently false that I mean to   select them all so we can use the B seven value  to determine whether we're going to select all or   select none so we're gonna say if B 7 value equals  true then D 11 through D and the last row equals   check that means check so then we're gonna check  them all otherwise uncheck them all here d 11th   otherwise so we're gonna check them all uncheck  using disk line of code based on the value of   b7 and we're gonna do the same exact thing same  code we're gonna call them I'll call Mel's gonna   be the same thing select dollars select none so  that's how we select all or select none now if   you look what this cool is when we uncheck you'll  see that the font changes automatically and to   check them that's how we differentiate we can use  conditional formatting to do that let's go ahead   and take a look into the home and go into the  conditional formatting and we're gonna manage   rules we'll take a look at that one rule to see  see how we did that all we need to do when we edit   that rule we're gonna see D 11 equals B dot that's  that same symbol that little B it's not really a B   it's check mark that's the same exact symbol for  wingdings that shows if it's checked and the most   important thing in here again two important things  we're gonna go over eleven must not be absolute   there must not be a dollar sign before 11 that's  the one very important condition the other very   important condition is that our database must R  applies to must start also on row 11 those two   things have to be the same and then it will work  just fine for each individual row so that's how we   covered them we've covered that for both the same  we got the same conditional formatting right here   so if we look under conditional formatting for  the manage rules for this we're gonna see the same   thing but in this case it's L 11 and our applies  to starts on L so it's the same exact thing so   that is how we covered that alright so now we've  got that we know how to let's how do we focus   lists how do we get these to move over here and it  could be a little bit complicated because I want   to make sure that I don't add the same employee  twice that's important if the employee hasn't been   added already I don't want to add them to the list  so we selected an ADD selected I want to make sure   to remove all the select employees from this list  and I want to put them here and so I only want to   have those that were not selected here and so if  I add two more I want to add those and I want to   clear the list and if I move them back I want to  move them all back here so if I unselect them and   select them what I want to do is I want to remove  the selected employees for example if I select   this I want to remove that select employee from  this list and move them over here so we add select   employee you'll notice 106 3 is no longer in this  list it's in this list so that's a really really   handy feature but we want to make sure to make  sure we don't have duplicates and the best way to   do that is to remove it from the original list and  put him here so that keeps us from doing it if we   right click on the add selected employees and we  can see what Macker has been assigned if you click   on the assign macro we can see we have payroll add  selected employees at slate in place so that's the   macro we're gonna be focused on and we're gonna go  into the VBA and take a look at that I think if we   scroll up here payroll add selected employees and  this of course is in our payroll setup module we   can have multiple modules on payroll payroll setup  is what we're gonna be focusing on so payroll adds   like in place the first thing we want to do is  want to dimension some some long rows pay row   we're gonna need to know that and the selected row  we're gonna need to know that also right so that's   important and then of course the last paid role in  the last liquor we need to know that information   and also we're gonna set a range found employee  ID range what I want to do basically is when I   select it I want to make sure just in case I want  to make sure that this deed neither one of these   IDs are in this list here so I need to check this  of tireless just to make sure because if the user   resets this list it's possible that the same name  is going to be here so we want to prevent that so   what I want to do is I want to put a search I'm  gonna say I'm gonna go through this list 11220 is   it in this list if not add it and then remove  it from here then we're gonna do the next one   one zero zero one is it in this list if not add  it and then remove it from here so that's what   we want to do when we go through the bth so the  first thing is we're gonna stop the calculations   because it's gonna be very slow if we don't stop  in the calculations the macro of course and that   macros can be found right here and all we're  gonna do with the stop calculation is we're   gonna enable events false we're gonna turn the  calculations to manual and the screen up in is   gonna go to false the only important thing when  we run this macro is that before the end of the   macro that we're currently running we must reset  everything and that means bringing events to   true the calculation back to automatic and the  screen updating back to true so when we run our   payroll we want to make sure that we reset those  and run the macro when we reset so let's take a   look at back and payroll you'll see that we have  stopped calculation but by the end of the macro   we will reset that's important if there's a bug  somewhere between your we cannot exit out of this   without resetting the calculation otherwise it  creates issues for the workbook because you want   screen updating you want calculations normally on  automatic but we're running this it's a lot faster   if we can temporarily stop those ok so the first  thing we want to do is determine our last rows I   need to know what the last row is here that's kind  of a bug you see that you shouldn't happen you   should not allow the user to select a cell that  doesn't have it so that means it's a bug let's go   ahead and fix that right now I want to make sure  that there's actually a value here before the user   can select that right we don't there's there's no  reason to users should be able to select without   a value so let's go ahead and do the developers  and fix that bug while we're working on it back   into the payroll here into the payroll right and  we have here we just went over that right if it's   nothing but let's add something to that here and  what we want to do is we want to say I want to   know the target row to the right must include a  value and right range actually we can do cells   cells what is the column target row target we use  offset also call them plus 1 dot value must not be   empty right we want to make sure it's not empty  in this an cells target our compost must not be   empty so that's the only way we want so now let's  try it out see if it works ok good not worse you   see now when we select nothing nothing or so we  can delete these so now it what it's gonna do is   gonna say if the target column if the target is to  the right is empty then don't do anything so now   it's like it now networks okay good yeah that day  we don't want to be able to select unless there's   actual employees but if there is one then we want  to be able to select it ok great so we've fixed   that bug live so let's go back into that let's go  back into our Mac we were focused on and take a   look at that so the idea was see payroll set up  here now is so we need to determine those last   rows both D and L the last row that has a value  I need to pull those up I also need to make sure   if the last Pedro is less than 10 then we're going  to set it to 10 we need to make sure that is there   we need to know the last row so that I can add  values right if the last row in this case it's   11 so I need to know where to put our next value  so we added I need to know to start at 11 12 and   13 if the last row is 11 so that's important there  so we need to know the last row and that we will   run a loop we're gonna start our selected rows  gonna be 11 starting 11 to the last selected row   the last selected row we need to check everything  so we're gonna say if D and the selected row is   checked right check then great because we need  to know I need to start here starting here all   the way if D if it's a check mark if it's got the  check remember check is here to be actually the B   you see that B here that's the check so if it's  checked then do something cuz we're only gonna   be focused on the check those are the only items  we want to add to the list so if it contains that   B I'll call it B I'm sure that's not the correct  name for it then we want to do is so we know we   need to check for duplicates I need to check I  need to know is 1 0 0 2 already here or not is   if it's already here don't add it so we're gonna  do is we're gonna set the found employee this is   a range and what's that range gonna be it's gonna  be M 11 through M and the last paper we're gonna   set that range that range is gonna start here and  it's gonna go all the way to the last row this is   our range but it's actually M sorry M contains the  employee IDs so this is our range it's going to be   a dynamic range based on the last row in this case  our range only contains three values so all I need   to do is in this range share of three values I  need to check is one zero zero two inside this   range and we can do that using the find find right  here find so we're gonna first define the range   then we're gonna find and what is it that we want  to find II and the selected row value that is what   we're focused on is here this is the IDE and the  selector this is what we're going to be looking   for we're gonna be looking forward in this range  so we can define that so that's the we're looking   at values and holes so we're looking in that range  now if found employee is nothing this means not   found not found right if it's if it's not found  if it is found if we do want to know if it's found   if not found is nothing that means it is found I  know it's so confusing not and nothing cancel each   other out cancel there's two negatives that means  if it's found right but we really want if it's not   found so I'm gonna take this out I want to know if  it's not found if there's nothing right if there's   nothing that means it's not found then we were  okay to add it then we can add it in so then we   go the last paid row equals the last payroll plus  one in other words I want to know the last one   was here but we want to add one because I want to  put the new value I want to put this one zero two   right here so when I click Add select employees  and put some ones or two in that new row so we   need to notice the last row plus one gonna put  it right here in column fourteen so that's how   we would do that in through the code so we crew  we take that last bit row we add one which is   going to be the first available row and then long  it says L and the last Pedro through and value L   through and equals D through F that's l l through  n LS right here L through n here equals D through   F equals D through F all right so that's how we  do it so if I select if I select an item and I   want to put that right here I just say click this  button and it adds it again L through N equals D   through F so that one line of code actually adds  it to the next line now I got to remove this I got   to remove it right from there right so let's go  ahead and do that route now what we're gonna do   is we're going to clear the content so what I  want to do is I'm going to clear D through H I   want to take this one that's just been added here  let's say we add 1 0-0 12 then I want to take this   I want to clear it out so D through HD through  H I want to clear that out right we just added   so let's clear it out with one line of code we can  do that here D range D through with the Select row   through H and the selected row clear clear it out  right I want it I want to remove it but now we've   got a blank space now I've got a blank space here  right just like this so now how do we get rid of   that Bank space well all we need to do is really  sort all I need to do is restore the list and   that'll remove the place so why don't we do that  let's go and sort that so to do that whoa sorry   first we're gonna do that for every if we're gonna  clear every item out every one we add it so if we   added this this and this and this right I'm gonna  add all those items then I'm gonna clear each one   out and the clear this so then we're basically  if let's do that let's before the sort after the   next right before the sort I'm gonna stop that  let's see what it looks like so we're gonna add   everything right alright now we've stopped it so  you see now we've got this cleared this cleared in   this cleared so now right we haven't sorted too  yet but all I've got is a bunch of blank spaces   they've all been moved over here but they're blank  so now I got to get rid of those blank spaces and   I can do that with sort all I need to do is sort  the entire table and it's gonna clear out those   spaces and we do that with the next cup of codes  sort first we're gonna clear any sort fields then   we're gonna add a key the key is gonna be f11 that  is gonna be our names right we're gonna say F is   where names or I'm gonna sort it by names which is  really convenient so we're gonna sort we're gonna   sort ascending lower to higher or a through Z  ascending and well then we're gonna run our short   range sheets so 16 of course sort we're gonna  set the range from D 11 to F and the last row   starting at D 11 and F in the last row we're gonna  sort that and we're gonna run that sort so when we   reset it now when we let's go ahead and move this  over so when we continue to run it it's gonna sort   that and everything's gonna all the blanks are  gonna be removed there now we're sort and of   course the last and most important thing we need  to reset the calculation and that's how we do that   so that's how we add items to the list we clear  the values out and we restore the list now we can   do the same thing for this so let's go bring those  back over with another macro now we have removed   selected about it so let's go ahead and take a  look if we assign that macro already been assigned   we can see the macro that's been assigned and this  one's called payroll remove select item employees   let's take a look at that very very similar and  that's here remove selected employees again we're   gonna stop the calculation we're gonna dimension  it very much the same we're gonna we need to know   both of the last rows both in both columns and  then of course if the last Pedro is less than 10   we're getting equal we need to make sure the last  Pedro is mm is not 2 and why is it 10 and not 11   because we add one right here so even though the  headers are in 10 because before we add anything   we're gonna add one so starting it off of 10 is  fine because we add one right here which makes it   11 so we're good again we're going to search for  it I don't want duplicates in this so we're gonna   do the same thing if let's say we select them if  one of these aren't equal here we don't want to   add it to the list if it's already contained in  that list so when we remove selected employees   I don't want it I first want to make sure they're  not in this list so we need to define this range   from the first to last row and look so we'll loop  through is 106 3 is it in here if not add it so   it's the same thing we do that for each one so  let's go back into the code here so we're going   to define the range here we're gonna select it  row 11 to the last Pedro this time is the last   Pedro this time we're starting here and going all  the way down to the last Pedro so the last time we   width from here at this time we're gonna go we're  gonna look through each one of these and so what   we're gonna do we're gonna first we're gonna look  for the found and this time we're gonna search the   range III less this time we're searching this  range under Eon this is the range I'm searching   before we search this range now we're going to  search this range and what are we looking for   we're gonna look for this one we're gonna look for  this list and we're gonna look for those checked   items if they're in this list then don't add them  if they're if they're not in then go ahead and add   them at the bottom so if found if it's not then  the last selected row equals glassworker plus one   that means we're gonna look for the last row here  we're gonna add one we're gonna put it right down   here and put that value right there so we're  gonna put whatever the contents here down at   the bottom of that one so we do that D and the  last lecture row + ethyl all the way through   F in the last selected row again here will be  here D through F equals whatever the selected   value equals K through n so that's how we do it K  through N or excuse me l through n here al through   in and that's gonna move the employee to the first  available row and again now again we're going to   clear out the move values I want to clear out and  this time we're clearing L through N and this time   I want to clear out thread so let's take a look  at that what that would look like and I'm gonna   stop the code before we run the store just like  we did last time so we see what it looks like once   they've been moved but not cleared so we're gonna  remove the selected employees we're gonna go back   into the sheet we're gonna see the blank spaces  we'll see they've been moved we're gonna take a   look at down here and we'll see they've been moved  down here all the way here that's probably why we   had selected last number because they've been  slow once we removed them maybe maybe I should   unselect them as they've been moved I think that's  a good idea if we move them back over maybe we'll   keep will unselect them not sure see which one's  the best but that's probably good idea because   we can't see them down here all right so next  up I'm not sure maybe you want them selected or   maybe not after remove them hard to say so what  we can do is now we're ready for the store now on   this e we're gonna sort these based on the name  and that's gonna clear out all the blank spaces   so as we continue with that macro down here we're  gonna run our clear our store fields and this time   it's going to be an 11 that's the name that's the  that's what we're gonna be sorting based on which   is an 11 the first the first value here we're  gonna sort it ascending again just like we did   last time and then we're gonna the entire range  that we are gonna sort is going to be L 11 through   and the last Pedro so as we continue that I'm at  macro again let's let's move this over I'm going   to continue that macro by pressing play and it's  going to sort all those items so now we have it   that is exactly how we run that and now we have a  set of employees now I want to add those employees   to the payroll so we selected all these employees  and now I want to step to I'm gonna go through and   and now I'm ready to go through these now I've  got these employees on part to review employees   and now coming up in part two next week I'm going  to show you exactly how we do save and next say   the next and next and automatically save this  employee payroll data so then we go through and   check it and all of course all the timeclock data  will show up here if there's any time clock did   it it would show up automatically so I can't wait  shut these that's going to be in part two of the   payroll which is going to be part sixteen of the  employee manager looking forward to bringing that   to you next week thank you so much for joining  me please share it and like it and of course   please check out the dashboard course that's  how we bring you free videos each and every week   and that helps us continue on to bring these free  videos for everybody thank you so much for joining

Channel: Excel For Freelancers

Views: 46,016

Keywords: Excel Payroll, Payroll in Excel, Create Payroll in Excel, Excel Employee Manager, Pay Employees In Excel, Excel Payroll Manager, Pay Employees with Excel, Manage Employees With Excel, Excel For Freelancers

Id: 8tcyr_QaqlA

Length: 74min 29sec (4469 seconds)

Published: Tue Dec 25 2018