How To Create A Physical Inventory Worksheet With Barcodes & Pictures In Excel From Scratch

hello this is randy with excel for  freelancers and welcome to the physical   inventory worksheet in this week's training i'm  going to show you how to create this incredible   inventory worksheet complete with barcodes  pictures navigating between previously saved   adjustments i'm gonna be able to filter based  on any type of heading including this and we're   gonna also be able to sort based on any type  of field in just one field it's giving me   incredible training it's all gonna be from  scratch so i cannot wait let's get started all right thanks so much for joining us this  week i've got a really fantastic training   counting inventory is a critical part  of any business you've got to know how   accurate your inventory is adjusting that  inventory is really really important so we're   going to show you how to create this inventory  automated worksheet so it's going to be easy we're   going to be able to print it we're going to be  able to adjust it we're going to be able to save   those adjustments and look those adjustments up  i'm going to show you a really cool filter tool   we're going to be able to clear the filter  add new adjustments printing them and a whole   lot more almost also how to insert these  barcodes pictures so i hope you'll stick with   us we're going to do it all from scratch so i'm  going to show you every step every line of code   every format and every formula so i hope you stick  with us on this training i do really appreciate   you sticking with us we are here every single  tuesday now this workbook is absolutely free   all you need to do is click the links down below  using your email or your facebook messenger and   we're gonna get that sent over right to you i do  appreciate these one way to support us is to do it   with our patreon platform in fact just last week  i created an update to a previous training where   we had this local business directory now for our  patreon members we got to now review so we added   this training last week but we added this really  cool reviews feature so we can automatically load   look up all the reviews on a per business and  create this really cool pop-up so if you like   this additional feature that is one of the many  features every week on our patreon platform   i create these applications and then update them  on our patreon based on your suggestions if you   want me to add a feature or you want to focus on  an area or maybe you want me to fix something i do   that directly inside our patreon and just like  that if you want this barcode these pictures   or any of the icons that come with these training  that's also available in our patreon along with   a ton of other features such as downloadable  workbooks we have downloadable video training   videos for offline viewing we also have pdf code  books and a ton of other features so i hope you'll   check us out there i'll include the patreon  link down below i hope you'll see us there all   right great so let's get started on this training  this is a sample this is what we're going to be   creating this is what we're going to be doing just  so you know i'm going to put this away and we're   going to start out with a blank sheet so we'll go  ahead and save it this is the sample i'll close   this up we're going to focus on this sheet here  this is where we're starting out so we know now i   did add a little bit of data in here so let's just  show what we have this is where we're going to put   our physical inventory worksheet of course we have  some data and that data is going to be items right   so our item list includes item name type and all  the parts purchase sales description cost and so   on and so forth quantity and stock and we have an  associated picture now that picture is based on   a folder right i've got that stored in one of my  folders and so as you can see inside this folder   is all of our pictures so i want to make sure that  we're going to need this path of this picture so   we know where to look those pictures up the names  of these pictures are associated exactly with the   names inside the picture right so that when you  get this file if you decide to join our patreon   platform you'll get these pictures and you can  test it out so it's a great way to test it out   all right we also have the adjustments what  are the prior adjustments right we've made an   adjustment we've adjusted it what is the date that  we made that adjustment on and what was the total   amount so we've got to keep that we also want to  know what items were adjusted inside that so the   individual items on the adjustment we've got to  keep track of those so each adjustment comes with   an id so this was all part of adjustment id one  these were part of adjustment id two and these   were part of three so we need to keep track  of each one i need to know the adjustment date   what is the id of that what is the actual quantity  what it was changed to right and i also want to   know what rows associated that's the database row  we're going to get into this a little bit later on   okay so let's start creating this from scratch  now and what i'm going to do is i'm going to   take those first two columns and just going  to color them gray because i want to make sure   that those are used for admin purposes only so  we'll color those gray and the first five rows   we'll give those a little bit of a different color  because i want to let's drop this down we're going   to be doing some formatting work i want to put  some formulas and some codes in there so we want   to differentiate that we're going to use this as  our header row i'm going to give this a little   bit of a darker color because i want to put some  headers in here so i'm going to have our headers   run across row 6 here put the title up here it's  going to be called the physical inventory let's   put the title up here i've already done a little  bit of formatting as you can see inventory and   then worksheet okay that's what we're going to  go to give it name and then what we'll do is   we'll put some buttons and an icon here i do want  to give it a nice background shape and then what   i want to put is some headers in here so here  i'm going to have the item id then i want to   have the picture here so it's going to be hidden  i just want that picture name here i'll skip there   and then i want to put in the item name here  the part number here i'll put in whatever   category here here we have and then what i want  to do is i want to put in the sales description   this long one sales description we can put in  that just we can we have enough room to spell   it all out that's fine then we'll put the item  cost here next up in column i want to put the   quantity in stock on how many are actually  in our current stock so we can compare that   then i want to put the barcode so we can scan  those in using a scanner if necessary the picture   will go in here in column l we are going to put  the actual quantity okay so that's going to be the   adjusted then in m what i'm going to do is i want  to know the value adjusted so i'm going to put   a formula in there we're going to call that the  value adjusted right so we know the value between   what is currently there in our actual amount i  want to put the item database row item database   row that's the database where it's located in the  item there and then if it's been saved already   inside our adjustment item i want to put that row  in there adjusted item database row i'm going to   put that in column o okay so what i want here  is the item database row that's going to be the   row that's located here it's actual row the other  one is going to be the adjusted row here so i want   to know what row it's in here great so that's  basically what i want our for our headers to be   and we'll give that a little bit of a color here  and so i'll just only these parts so i'm going   to right click and then format those cells then  we'll just use a theme here in this one maybe   we'll use this one right here this darker theme  here and then just we have it actually we'll go   with a fill here and then what we'll do is we'll  do a fill effects and we'll do a two color fill   effects here so we'll go from this one to the  light one or the light one to the darker one   that should be fine so we can see that because i'm  gonna have a top header too so we're gonna do that   and then that top header i'm gonna put i'm also  gonna call that we're gonna call that the physical   inventory worksheet so that we can see that on  the printout so i'll go with this a little bit   darker that darker theme so we're going to use  the same color theme but just a little bit of   the darker one here so that it blends in properly  click ok all right so what i want here is the name   probably right around somewhere around here we're  going to call this the same thing we do a physical   inventory worksheet and now what i have they'll  just put some borders around all of it so it's   nice and clear and then we'll clear some borders  out because i don't want them all format those   cells and then i'm going to do some borders we'll  do this brown color let's bring this up so you can   see it i'll give it this keep it the same theme  which is this brown color here and then i'll just   color it that thin border all the way around in  the inside and i wanted to want to remove a few of   these borders here these borders we don't want to  see here so we'll keep those up i do i don't want   to merge and center these so we'll just remove the  borders from here that should be sufficient not   there and that should be sufficient there okay  so we're good to go we've got our little color   i also want to give this a nice background so i  want to you go into the page layout and give it   a background so when i select background here i've  got a white paper background here that's kind of a   cool so it gives it this background so now it's  kind of a nice and i also want to remove those   grid lines so back in to the view here and we  want to move the grid line so we get a nice look   we're going to put some button sets up here i'll  put an icon here so we're going to let's go ahead   and add the buttons now and add the icons after  because we want to make sure the buttons are below   the icon so we'll add some shapes here i'll use  a rounded uh rectangle corner here and then i'll   give it a height of about point three let's  say point three one that should be sufficient   the width is gonna and we also want the font in  uh white so we're gonna call this clear filter and   then what we'll do is i'll just place this  directly in the middle here and then on the right   and then we have space for icon so that's pretty  good and then what i'm going to do is i'm going to   use ctrl d i'm going to duplicate that and we're  going to call this the next one we're going to   call this new adjustment so this one's going to  be our new adjustment we're going to use that   one to create new adjustments you saw it work in  the sample so new adjustment and we'll set set the   width so we have space for the icon and i'm also  going to duplicate this i also need to we need to   print it out right so we're going to use control  d print and then worksheet so we've got to print   it out again one more duplicate i want to now save  it so we need to add one for save called the save   adjustment and then also what i want to have is  i want to have some navigation some arrows so   again we're going to insert a shape instead we're  going to use this arrow here we're just going to   create an arrow but i don't want i want to save  room for some text so i'm just going to use that   up like that and something like that would  look a little bit good but not quite that much   and then a little bit down like that so what i  want to do in this case we can zoom in if you want   to set the size right i want to call this previous  so we'll do pr previous and then i'm going to make   it a little longer so it fits all the text in here  not previous previous so now that we have that   button and then i want to do also i'm going to  center this here just like we did here just like   we did with the others and that looks pretty good  we can reduce it a little bit and then the next   one's obviously going to be called next so now i  don't want it if you see i don't want i want less   spacing around it so what i'm going to do is i'm  just going to right click i'm going to format that   shape before i create the next one and then we're  going to go to the text box here and we're going   to reduce the left point zero three point zero  three that's to be sufficient enough so that way   we don't have so much spacing around the button  there okay once we're set up with that then we're   good to go with that again we wanna make sure it's  centered i like that there centered in the button   so i want to do now a next button so the best way  to do that this is really our next button so i'm   going to duplicate that and then what we want to  do is we want to call this one here let's move   those out of here i want to switch those right  so our previous here is going to be aligned what   we're going to do is we're going to rotate this  we're going to flip it horizontal so our previous   looks great now all we need to do is set our  next move it over here and set our next one here   change this text to the next so now we've got our  navigation buttons here so we're going to change   this to next okay so all we want to do is line  it up so i'm going to grab my selection tool here   i'm going to line all these up here select  them and then we're just going to make sure   that they're all in the same line okay that looks  good there i like that we'll undo the selection   tool here and then just move these a little bit  closer there so we're going to be able to navigate   between previously saved ones using the next and  previous so we'll save an adjustment we'll print   the worksheet here new new adjustment and we're  going to clear the filter okay so now we're ready   to add some icons here so i've got some saved up  so all we're going to do is insert pictures here   and then i'm just going to select holding down the  control all these icons that i'll be using inside   here and then just click insert okay obviously  there need to be size so we're just going to set   0.2 going to be the default size except for that  larger icon that we're going to use for the shape   so i'm just going to drag them over here that  sheet icon obviously i want that bigger so that   one's going to be bigger here that looks fine that  looks sufficient for us and now i'm going to move   these buttons up but let's put these icons here so  i'm going to have this clear filter button you can   barely see them because it's white on the right  i'm going to have this one let's take a look at   that that's our print so i'm going to bring it  over here this one's for our new one i know it's   white you can barely see it this one i'm going to  use that for the clear filter so that one right   we're going to clear the filter so it's going to  use that recycle bin along with the filter so i'm   going to cut that in half the size and then we're  going to drag it down here so right so that's the   clear filter we're using two icons for that and  then another one we have here inside here we have   the save adjustment so let's pull that up zoom  in here's that check box here almost can't see   it there all right we're good to go on that so  i like that so now what we're going to do is   we're going to group these buttons and then we're  going to size them accordingly so we can zoom out   and now what i'm going to do is i'm just  going to hold down this and then i'm going to   center that and then i'm going to group them i'm  going to do the same thing for each button here   making sure that they're both centered and then  both grouped and then we're going to do the same   thing for this center it and then group that okay  so now that we have all of our buttons i'm going   to keep the clear filter a little bit over to  the left but i want to space these accordingly   although they already are i'm going to hold down  the control and i'm going to go into format and   i'm going to do line and then what i'm going  to do is i'm going to distribute horizontally   it's going to include the spacing there okay i  like that everything is looking good we've got   everything grouped here we've got to group this  one too so i'm going to hold down the control   making sure to select each shape inside there  and then we're going to use we're just going to   be able to group that okay so now we've created  our buttons and what i want to do is i want to   have some filters up here as you saw i want to  have this is going to be called filter by right   not goodbye filter okay so we already have a  drop down list assigned to that as you can see   but i'll go ahead and walk you through that then  we'll also i want to have this one called sort by   then i also want to know the total adjusted so i  want to know that so the total adjusted value so   i'm going to put it here total adjusted value  then i'm going to right justify as it already   is so that total total adjusted value is going  to go directly inside here all right so that's   going to be sufficient so this one so let's say  sort by this one and our filter by should be   colored in white those are going to be user  entered so we're going to close those white   then i'm going to format those using our same  border that we've been using consistently and then   i'm going to use a dotted line on the left side  and i'm going to use the solid on the top right   and bottom okay it's already on the bottom okay  i like that that's going to give it now according   to this we just want to use the filter by and this  and not this one the filter by the sort by and the   total adjusted value here i also want to give  those format those cells so we're going to use   those borders we're just going to go all the way  around here and then use the dotted on the right   side so we're consistent and then we're going  to click ok all right so i like that this total   adjusted value right here i want to format that  cells i want to put the border on top but i want   to i don't want to put that one in in white that's  going to be a formula so i want to make sure that   that doesn't go white because the user will  not be entering anything in this adjusted value   okay i like that there these are not part of  the header so we can i'm going to drop this down   here so i don't need that so we don't need the  header to extend beyond that it's not necessary   so all right we're good to there let's save our  work and now what we're going to do is we're going   to just continue we can bring these button sets up  a little bit so that they're balanced but i think   we're good to go on the original design i do want  to add some conditional formatting in here as we   add data in here we want to make sure to do that  so i'll do the unselection just bring these up   a little bit here and then we can all right that  look good right there all right so what i want to   do now is i want to make sure that we're going  to add in data but let's add some information   inside here okay so how we going to do that well  what i want to know is i want to know what sort   column this is going to help us when we know  which corner we'll be using this for this so   i'm going to put a sort column based on that sort  and the second one i want to know if there's been   changes this we may or may not use but i want  to know and i'll explain why and it's up to you   how you want to do it i'll explain some  differences whether we want to know whether   the user has made changes or not so it could be  important we'll go over some ideas together so   i also want to know the adjusted id if it's been  saved before it's going to contain an id 1 2 or 3.   so that id is going to go directly inside b3  also i want to know if it's been saved before   i want to know what row adjusted id row i'm going  to put that here and i also want to know the next   id number so i'm going to get borders around these  and then we're going to we're going to go through   just a few formulas and some named ranges  so if you go into the formulas name manager   and you see i've got i've created a few just  to help us move along i've got an adjustment id   now if we look at that it's an offset formula  based on the adjustments id one two three right so   that's an offset so they're tied so as the  adjustment ids increase so does this formula   if we tab out we can see the dancing ants are on  one two and three there i have the same thing for   item id i wanna know know created all the item ids  and i also have an entire one for the item data   we're going to be applying that item data and i'll  show you so this named range item data encompasses   all 16 columns of all the item data and as many  rows as we have so it's an offset formula using   a dynamic number of rows using count a and all 16  columns so this particular named range encompasses   all data okay i don't do that too often because  we then have to set the columns but sometimes it's   important and i'll explain why okay so we're going  to the data as we enter the data the data is going   to come in here in other words as we list all  the items it's all going to come here item id and   picture are not important as far as the picture  name or the item id so those could be hidden right   so the item name this is important the part number  everything else here is important and so what   i'd like to do is i also want to have a picture  folder right if we're going to be adding pictures   in here remember those pictures we need to set  a folder where are those pictures going to go   so i'm going to put that directly inside let's  say oh i'm going to put in the picture folder   and then i want to have that picture folder here  that label here so i'm just going to copy the   formats here i'm going to use the same formats  here paste special and then paste those formats   not the validation but just the formats so we  have that here so what i want to do is i want to   put that file now you can add a browse button you  could browse for the folder but it's relatively   the same thing as doing this so all i want to do  is take that full file path i'm going to copy that   and then i'm going to paste it directly inside  p4 okay so now we have our our full file path   directly located in p4 so once we have that  we're going to be able to map those pictures   to that specific folder all right so then we  can get a full file path using the picture   name now that picture name is going to  be here that combined with this folder   will provide us the picture and then we can place  that picture directly inside here but i also want   to add some conditional formatting as i add name  whether it's based on here as i add item names   i want conditional formatting here based on that  so how we going to do that well the first thing   is i want to place all of those let's say all  the way through m and down i'm going to add some   conditional formatting so i'm going to go into  the home and then conditional formatting i'm   going to add a new rule it's going to based on  a few conditions so it's going to based on what   conditions well it's going to be based on two  conditions i want to know is there a value in   column d and is it an odd or even row because i  want to color them accordingly so it's going to   be based on and equals and and what is the first  one is there a value in d and the row but it's not   only row 7 it's every single row that we're  applying so i'm going to remove the dollar   sign the absolute there and so i want to make sure  that that is not equal to empty and i also want to   make sure that it is an even row so in this one i  want even rows so that's mod formula we're going   to use that the 0 indicates even rows so what do  i want to color that well i want to color that   let's say that white color i'm going to give it a  white color so i'm going to format that i'm going   to give it a fill of white and then i want to give  it a light border something relatively light but   you know enough so i'm going to use our same color  here probably let's say a little bit lighter color   here and then just give it that light dotted line  all the way around so that's going to give it the   border now what i'm going to do is i'm going to  copy this and i'm going to ctrl c and click ok   i certainly want to apply that to all of them  so when we manage rules we need to do more rows   so i'll do 999 i want to add one more row i want  to add one more conditional format because we've   only worked on the even rows but what about the  odd rows so we're going to go into new rule and   this is why copying that formula is so easy i'm  going to paste it in right here but this time   we want odd row so i'm going to do that 1 here  and now i'm going to give it a format i'm going   to give it a fill i'm going to use our consistent  color here something like that and then that's it   i also want to give it the same consistent borders  that we did with the other one so believe it was   this color we used here and i want to go all the  way around just like we did here and click ok   and click apply okay and click ok all right so  now we see we've got so now as we add items you   see our conditional formatting applies here let's  double check this this didn't apply so notice that   it didn't apply so we just make sure we have to  update the range the apply to range for the full   compass so managing those rules we need to make  sure that we're going to copy this applies to   to the entire range too click apply okay good so  now we've got our conditional formatting i like   that so as we add items our grid will grow out  and it looks kind of nice so the macro is going   to take care of that it's going to take care  of adding items from here into our inventory   worksheet based on maybe a specified filter  so now let's take a look at this i've got some   there's no specific range tied this but what i  want there's no named range here notice i'm just   clicking here but what i do is i want to set a  named range right what do i want to do i want to   basically filter by any of these headers right  pretty much any of these headers here i want to   filter by so if i take this right and let's just  say we call it headers right or we can call it   if we go into the formula and name manager we  see we've got one called headers already here   i've created it it's based on that range just to  make things easier so all i need to do is go into   here into the data validation here and then just  make sure that etc i had a few different ones this   we're just going to call it equals headers if you  want to find it you can use f3 to find it now when   we do that you see we have that here so we can  filter now what if we want to sort by any of those   well sorting by i'm just going to copy and paste  the and paste it all there so now we can also sort   by headers right we can sort by item name we can  sort by part of course of course we need to to to   do that and we need to add some code to make that  happen but that's you get the idea okay great i   also want to know the value the adjusted value  we get to a formula in here i'm also going to   be bringing down formulas here and here and i'll  show you how that's going to work but what we want   to do now is we want to get that data into that so  how are we going to do that we're going to do that   with a macro we only have a few formulas let's add  in some in the admin columns here so what i want   to know here is i will put in let's say we have an  adjustment id we have our adjustments here one two   and three so i want to know if it's three i want  to know that that's on row four so if i put in the   id here i want to know what row it's on and i want  to place that row directly inside b4 so we can use   a match formula so we're going to do equals and  then if air we don't want to show anything and   match what do i want to match i want to look up  whatever is here and i want to look it up what   is the range i'm going to look up and of course  that's going to be our adjustment id here i want   an exact match but i want the row number so those  row numbers they start on three so i'm going to   add 2 to that if it's an error i just want to show  blank so that means adjustment id row 3 is on row   5. double check that here and we see that row 3 is  on 5. that's what i want i also want to know the   next available id we know it's for because we just  looked at that but how do we know in a formula   well we can use equals if air again we use the  max formula we're going to max i want to know the   maximum adjustments id but not just the maximum i  want to know the next one so i'm going to add plus   one however if there's an error well why would  there be an error well there'd be an error if   there's no data and if that's the case i just want  to show one okay so the next available is four   okay perfect so we have that and then what i want  to know is the sort column right what i want to do   is maybe we'll get into this in a little bit as  soon as i show that we don't need that quite now   but i'll get into this formula but let's just get  these items loaded in here saving our work here   and then we'll go into the item so basically what  i want to do is i want a macro that's going to   basically load these items in but it's got to be  based on some filters right we have filters and   sorts here and we're going to use some criteria  here so i want to base it on certain criteria   right i want to filter it maybe we're going to  filter by some certain let's extend this around   let's put this one here i need to know what we're  going to be filtering it by so let's add this   it's going to be white so the idea is this if i  want the user to enter some sort of a filter we   want to make sure that they can enter some text  so if they're going to filter let's say they want   to filter by item name and then they put the item  name in here this is going to be that filter just   as you saw in the sample so that's what filter by  and then whatever however if it's like item cost   maybe they're going to do something like greater  greater than 30 right so it's going to be kind of   different so the idea is a little bit different  based on the filter if it's an item name a part   number a category or a description those are all  those first four are all text text based types of   filters and that means if the item name contains  this if the part contains or the category contains   some text then i wanted to show it however item  cost and quality and stock are both numerical   types of filters so they need to be treated a  little bit differently so what do i want here the   first thing this is our criteria item cost right  this is our what we're going to have our criteria   there's our criteria header and our criteria our  actual criteria so i need to make sure that when   we run an advanced filter our header is this e4  and our the results our filter is going to be here   so inside our items i need to do that right here  so what i need to know inside s2 our criteria is   basically whatever is in e4 right whatever's  in the inventory worksheet e4 therefore if i   change it here and i change that to item cost to  quantity in stock and i go back to items we'll   see that that's automatically changed then we  have the results now we need to differentiate   is this greater than 30 but what if it's a text  based filter what if i'm filtering let's say i   want to know the item name and i want to know  any item name that contains the word bath right   in that case what i want to do is i want to put  those wild cards both before and after the text   so they're treated differently that filter is  treated differently based on the type of that   so if we know that right and we know which four  are text and we know which two are numerical then   we can make decisions based on that and we can do  that just right here if first of all if inventory   f4 equals empty then show does not equal why would  f4 be empty well f4 is right here if the user has   not entered anything there then i want to make  sure to have something like does not equal empty   so that all we need to do is just enter less than  or greater than and that's going to contain so   every field that doesn't contain empty will show  up here so we want to make sure that also however   if what if it's going to be quantity in stock  what if we want to deal with those two numericals   those are quantity in stock or item cost in that  case if e4 equals quantity in stock or it equals   item cost then what i want to show then i want  to show exactly what and i also want to make sure   that inventory worksheet here and so it's an and  so it can be or quantity in stock or item cost but   and it's also separated by and here so and we  want to make sure that f4 does not equal empty   right we want to make sure that we're actually  adding a filter in that case what do i want to   put i want to put whatever's in f4 whatever is  in f4 meaning if let's go back to where we were   item cost greater than 30. so i want to know  whatever whatever's in f4 we are going to place   directly in here exactly as it is however if it's  a text base we need to put the asterisk before   and after it right just like we had here so that's  what we don't want to so we can't expect the user   to put in the asterisk here so we need to do  it through a formula and we're going to do that   through here so now otherwise here's our otherwise  right here else here's if it here's if it's true   and here if it's if it's false if it's false then  what do i want to do that means it's not quantity   in stock and it's not item cost not either one of  those then it's a text base so then i want to add   an asterisk before then i want to enter whatever  is in f4 and then i want to add an asterisk after   both of those instances so that's for text space  so we have a single filter handling both text   based and numerical type filters when we run the  advanced filter this will be our criteria it's a   dynamic header dynamic contents our results will  come in here we then want to take these results   and we want to bring them directly inside here  starting all the way on column a and bring it   all the way down here up to quantity in stock the  barcode the picture our actual quantity will be   entered by the user and our adjustment will be a  formula so i'm going to need a formula here and   i'm going to need a formula here a formula i'll  show you explain why the formula we need for a bar   card and we're going to need a font on that okay  so how do we do that well first let's go ahead and   get that data in here and we're going to write a  macro to do just that so we can pop this up here   we're essentially done most of the formatting and  inside the developer using alt f11 is a shortcut   there will get us into that and i want to write a  few macros now i've got some information here just   to help us the macros we're going to be writing  but i've got some guidance that's going to help   we're going to start off with load items and then  we're going to go to add new after that but i do   have some dimension variables already picture  folder as string picture path a string we're   going to need those for picture for last row the  item row last results row adjustment database row   adjustment row item database row adjusted id is  long and the sort column is long and then the   picture shape as a shape great so let's focus on  this second macro here and that's going to be the   inventory adjust and load item so this is where  we want to load all the items based on a filter   and then we're going to sort it accordingly right  we want to sort it we're going to also have to add   in the sort so let's go ahead and do it we're  focusing primarily in the beginning with the   inventory worksheet so with inventory worksheet  just to make sure you have the sheet right put   the dot if the intellisense shows up that means  we have the correct sheet name okay the first   thing what i want to do is i want to know if the  user has made a changes and here's here's where   we could run into issues and something that i'd  like to recognize let's say the user is making   some changes on here they're putting the actual  quantity right as opposed to the quantity in stock   and then they click new adjustment if they click  new adjustment everything's going to erase right   it's going to clear out because they're new  but what if what if they clicked it by accident   i kind of want to give them a way out saying  hey you've made changes here do you want to   save those changes can you imagine they have  500 items they've made a change to all 500 or   and then they accidentally kicked the wrong button  everything they just did is gone right so we have   to understand how a user could work what kind of  mistakes they could make and they might click a   button by accident we want to give them a way out  so to do that or when they're loading a you know   when they're loading new adjustments this is the  macro that we're going to tie to that so we want   to anticipate what mistakes they could make and  then help them on that so to do that we'll just   give them an out so what i want to do is when a  user makes a change here we're going to set this   to true changes that means they've made changes  right now let's say it's false as soon as they   make changes to anything there but those changes  only come on a load when those changes are made   right i want to make sure the user knows hey b 2  is true you've made changes are you sure you want   to erase all those changes so that's the first  thing we're going to do inside this macro check   to see if b2 is true so back inside the vba code  we're going to do just that so if dot range b2 dot   value equals true then we're going to let the user  know with a message box changes have been made to   this adjustment are you sure you want to clear  these changes without saving because that's very   important right put a question mark there and of  course it's going to be a vb yes or no question   and then we'll give it a title do changes not  saved then we sell equals vb if it equals what vb   no let's say then we want to say unsafe changes  right we want to make sure i think i had to have   to add a parenthesis right here notice that  that didn't come up right sometimes you're   doing intel sets and you make a mistake i do all  the time so it's just the equals notice nothing   came up so i know something's wrong with my  code so that even before i get to the end   i know that i've missed the beginning of  the parentheses here so when i put that   in right and i know now when i put in equals  you see how it comes up you see the difference   so now i know that i've you know messed something  up and you can go back that's a good way to avoid   then right exit the sub right if they don't want  to save those changes are you sure you want to   clear these changes if they say no i'm not sure i  want to clear these changes then exit the sub so   we're exiting out okay all right great so let's  fix that then exit sub we need to we need to go   um back to the beginning make sure we add a if  there if right because if then statement okay   great so then we're going to exit the sub that's  just in case there's any unsaved changes okay now   we can continue with that the first thing what we  want to do is you notice that there was pictures   inside that so we want to make sure that we've  cleared all the pictures and cleared all the   data if there's any existing pictures i also  want to turn off screen application making   that updating default so application screen  updating equals false so we're going to turn   that off and make sure before the end of the  macro we always want to turn that on so we'll   just application and we also want to make sure  there's no exit sub screen updating equals true so   what we don't want to do is exit the sub anywhere  inside here without actually turning on this so we   got to be careful of that okay all right so let's  continue on so once we turn that i want to clear   all the existing data so to do that we're going  to do dot range and then we're going to do   uh clear which data so let's do this which data  i want to clear a7 we're going to start out the   top row all the way through o and then a large row  o and then we'll just go 999 and that'll be fine   okay so we've cleared that and not value but  dot clear contents okay so we're clearing out   all the content so we don't want to use clear  that would clear the formats out clear all   existing data once we have that now what i want  to do is i want to remove the existing pictures   and i want to give them a specific name once  i assign those pictures specific name i can   remove them based on that specific name now  you'll see picture shape i've got that as a   shape so we can do a use a for each loop four  each pick shape in right dot shapes right so   for every next picture shape close our loop  okay so inside that loop what do we want i   want to check for a specific type of shape  i would not every shape if using in string   in this case picture we'll call picture shape dot  name we're looking for a specific name and what am   i look i'm going to call it item pick okay if it's  greater than zero meaning it meaning it does that   is inside the name then i want to delete the  shape then pick shape dot delete right delete   picture shape okay so that's all that's going to  delete all the picture shapes let's just fix that   up don't forget the then don't forget the then is  greater than zero then okay now we got it correct   correct okay so now that we have that what i want  to do is i also want to set a start column now   what is that start column going to be let's add  that in well first of all we haven't done it yet   but i'm going to show you exactly how that is  okay so we're going to set the source excuse me   sort column the sort column i don't need to what  column equals dot range just set it to b1 and then   i'm going to add that formula in the b1 that's  going to be our set to sort on sort set sort call   okay so the idea is this once i have the data  inside here inside our results i then want to sort   it based on those results so we're going to run  our advanced filter we're going to get all those   results and then i want to sort those results now  you notice that our first possible sort is item   name right the first possible one we sort right  the first one is item name item name is right here   i want to sort based on that so if they choose  item name that's going to be our first one right   our first one so remember we have a name branch  called headers so if i use so let's say equals   match will wrapped around if air which i always  like we're going to run a match right and now   i want to match i'm looking up what they've  chosen here i'm going to base it on headers   okay and i also want an exact match if it's  an error just show blank so right if they show   name if the part number is going to be 2 category  is going to be 3. okay however i don't really want   the 1. what do i want i want to know what column  we're sorting by for example item name what column   is this if i look down here equals column i know  this is column 30 so we're going to sort by column   30. how do we get column 30 from item name or how  do we get column 31 from part number well all i   need to do is add 29 to this so if i go here and  i hit plus 29 i will always know what column we're   sorting on if we're sorting by any name it's going  to be so column 30 and if it's going to be part   number it's going to be 31. great so now i know  what column that way when we get our results here   we have already filtered them i want to know  what column to sort it's going to be one of   these columns right here how do we know which one  so now we've got the column and i know the row   so we're going to set that into a variable just  like we did here so b1 is our sort column number   so we've got that we've put that into a variable  inside our code so we've done that already   now continuing on with the code sort column once  we have that that's it for our worksheet for a   while we're going to work with the data so right  i want to work with the data so we've covered   everything for our inventory worksheet for a  bit now what we're going to do is we're going   to focus on the data so that's our item data so  with items just to check item data that's what   we're going to do first of all i want to know  what the last row is the last row of the data   is going to be based on and excel up okay so our  last row of data i used auto hotkey to make that   really fast all the hotkeys are free software you  can get it okay so what if our last row is less   than 3 then we're going to exit the sub there it  is again be careful of this exit the sub we can't   so let's say we we want to we don't want to exit  the sub we got to make this true so you can go to   something like this then go to something like no  data right then what you do is you put no data up   here so everything is skipped and we can skip to  that so be careful right we cannot exit the sub   without app without updating this now we could say  then and then put this in here then put copy this   and put it here and then use an end if but this  is a little bit quicker just easier okay great   so we have that that means there's no data and  we'll do that with the results too all right so   we've got our last row now we're ready to run our  advanced filter so our advanced filter is going   to be all right right here so once we have our  advancer where are we going to run this data let's   take a look at the data and we're going to bring  this down and take a look directly at that data   inside this sheet we're going to start it out on  now notice we have a blank column here why do we   have a blank column here because our results also  contain a blank column column c so i want to bring   all the data over into one shot so if i've got  a blank column here item id picture blank column   i want our results to match that item id picture  blank column if our results contain a blank column   so must our original data our original data must  contain a blank column so how do we do that we're   going to run our blank our original data all the  way to q because it contains a blank so it's going   to go all the way from a2 through q in the last  row so let's write that in inside our code here   so we can update that so all the way from a2  through q is going to in our last row once we   have that what about our criteria our criteria is  going to be s2 i use autohotkey to make that quick   through s3 that's our criteria what about our  results our results are going to come directly in   all from a a through a j a a three a a a two all  the way through a j two so we can update that   inside the code right here so a a2 through aj2  all right so now what i want to do is i want to   determine the last results row of our results so  how do we get that so last results row is going   to be equal to not a a oh yeah it is a that will  work a a okay that's our last results row what   if it's less than three if our last results row  is less than three then we know that we have to   exit the sub again not exit the sub but go to node  data so we can copy this and go to node data right   if there's no results if it's less than three that  means we have no results so we cannot continue on   okay so now that we know we've got results what  i'm ready to do is bring over those results but   before we do that we certainly need to run that  sort right we have to sort data based on whatever   the user has decided okay so now we're going to  run our sort so i'm going to put a comment here   run sort okay so with dot sort we're going to  run that sort sort and everything we're going to   do is inside here now so with the sort what do  i want to do the first thing what i want to do   is i want to clear that sort so sort fields let's  bring this up here so you can see it a little bit   so sort fields dot clear so clearing that clearing  out any previous sorts the next thing what i want   to do is i want to make sure that we're going  to add a key so dot sort fields then dot add   we're going to add a key so what is that key that  key is going to be equal to focus on items right   items but we can't use range because both the  row and the column are dynamic so in this case   we'll say dot cells and what is that row we know  the row already it's going to be that first row of   data that first row of data is right here 3 right  here on 3. so we know if we know the first row of   data is on 3 then we can continue on so 3 is that  first row data what column are we sorting this   is where that variable comes in handy that column  that we're sorting is the sort column the one that   we've divided up here okay so we have that now  what are we going to sort on we're sorting on what   values so we're going to sort on equals excel  sort on values and then what i want to do i want   to set the order so what is that order going to  be that order is going to be based on extending   xl ascending great and then i also want to undo  the data option data option is going to be normal   right so equals excel normal sorting on that data  and that's it so it's going to sort based on user   entry okay so whatever this sort once we said  that we want to set the range so the set the range   is going to be equal to the items we need to call  out that sheet because we're inside to sort with   dot range here we can use range because we know  it's going to be aa3 and then all the way to the   end our last one a let's take a look at this so  we know the last one here a j so aj is our last   row i'll bring this up here all the way to the top  okay so now we've got both our sort range so a j   and what what is the last row and the last results  are a last result row okay so great so now we've   got our range that we've set and now we're just  simply ready to apply it so dot apply is going to   be our range so now we can apply it so that's the  end of our sort so that'll automatically sort it   once it's sorted we are then ready to bring in  our data so let's do that right now so we're   going to bring in the data so we're going to focus  again on our invoice worksheet invoice worksheet   then what range do we have we're going to start  of course in range a 7 that's our first row a7   and where's it gonna go all the way through  j and the last results row plus four and   last result row plus four why are we adding  four because our results here start on row   three here right and our results start on row  seven so we need to compensate for that so we   do that dot value is equal to based on our result  data so our result data is going to be dot range   aa3 same as our range a3 all the way through a j  in the last results row and the last result row   this is going to bring over our data okay bring  over data but not everything we still need to   bring over a little bit more over data so what  else do we need to bring i need to bring over   the item database row so bring over item data but  also what else so that's going to bring it all the   way over from a through j but we still have one  monocum right so here's our information it's gonna   bring it all the way up from a all the way through  j so the barcodes here are a through i actually   sorry a through i here so once we have that in  stock i also want to know the barcode is going   to be generated the picture the actual quantity  the value adjustment that's all we don't need that   but i do want to bring in this item database  around column n so let's do that so it's going   to go to i quantity and stock is i so that's  the one the last one we're going to bring   barcode will be generated using vba so no  problem on that okay so how are we going   to do that well we can just update that with  one more item so this is going to be i not j   alright and it's going to come all the way making  sure that we have that here and through i and our   last column is j so that row aj is going to be  brought directly over inside here n so let's   do that with one line of code so all we need just  to bring on we can just copy the existing one and   make the other adjustments so let's just copy this  so we can do a little bit less writing paste it in   here and update the arrow so right this is going  to come directly from this it should be i here and   this should be j and this should be j okay so so j  aj through aj in the last row so we have that will   bring it over and it's going to go directly into n  column and here's where it's coming from not a and   n okay so we have that there so now we're going  to do is all just one that column this shouldn't   be a i this is oh correct a i it should be just  i not a i so a7 through i in the last row that   looks correct all the way through aa through  ai and then n7 gonna bring j so that's going   to bring in all of our data bring the last one is  going to be the database database rows bring over   item database row okay so that's it let's saving  our work okay we're going to run this as a test   and see how it goes but we also want to do let's  bring over let's see how that goes check for any   issues on that and we'll just update that excel  sort number not excel number excel sort that'll   be fine and then just continue on and okay let's  take a look inside our original and see how we are   okay let's take a look here we've got our ids here  we've got based on our results based on the bath   right we had a filter set up so it's only those  rules part number assembly bathroom okay so let's   get that barcode in there now to do the barcode  what we really want to do is use a barcode font   now i've provided one already in fact we're going  to use what's called free three of nine now if you   want to use that i've got this barcode font called  free through night and i'm gonna supply it inside   if you don't have it i'll it's free of course you  can download from the internet or i also have it   available on patreon so i'll make sure to include  that free three of nine and if i use that as a   font here it works great but what i want to do is  i want to make it based on that part number that   barcode is going to be based on that barcode now  using free 3 of 9 to use it properly we need to   add an asterisk before and after it so for example  equals right and we do uh asterisk let's put in   quotations marks asterisk and then what we want  is another end quote and we want the part number   then and and we want another asterisk in there so  it's got to be separated by asterisks both before   and after and that's going to give us our correct  barcode this is correctly related but what i don't   want to do is i don't want to have this formula i  like to be able to clear it out so i want to put   this formula somewhere where i can bring it down  so that's what i'm going to do i'm just going to   copy this formula here and i'm going to paste that  directly up here paste it directly inside here   and we're going to show now let me clear that i've  got a format here but i don't i'm going to put   general because i want to show it to you i already  had it there we go i'll show you how to hide that   okay i also want to formulate here i want to know  the value right we're going to put a picture here   if for example if the actual value let's say i've  got one here or let's say i have two here but   our quantity stock shows zero i want to make sure  that we reduce our value by how much how what's   the value adjustment so it's going to be basically  a formula to do that right it's our total   equals so it's basically this equals this  minus this so our our current quantity   minus our quantity in stock multiplied it by  the price right by how what is the price here   so the price is this cost here so that's going  to give us our actual adjustment right where   if we show we have four in stock but where actual  quantity is only two that's a loss of 798 dollars   so what i want to do is i want to take this and i  want to place it inside here place it inside here   basically all the cells but again i don't want to  place it i want to be able to clear out the data   so how do i do that well what again i want to do  is i'm going to place it directly up here and then   i'm going to use vba to bring it all the way down  so i'm going to copy this here and i'm just going   to place that directly inside here and again what  i want to do is i want to show this so here's what   it should like so how do we hide this now we've  used conditional formatting to hide in the past   but what i want to do is i want to hide it so i'm  going to hold down the control and i'm going to   give it a custom format just like you saw a minute  ago but you didn't know what it was and we're   going to go custom and then what i'm going to do  is i'm going to add a specific format and which   is going to be three semicolons one two three  semicolons enter and that's going to automatically   hide it very cool huh i haven't used that before  three semicolons will automatically hide it right   conditional formatting is another way so now  it's hidden now we've got those formulas hidden   of course you if you're you would protect these  cells and so now all we need to do is use vba to   bring down these formulas and it'll populate both  the adjustment and this that way as soon as they   add in an actual quantity it'll be automatically  updated just like that and of course if there's   the same there would be no adjustment so that's  what i want but i want to do it through vba so all   i need to do is have the original formulas here j1  we could do j1 and m1 or j1 through and one either   one would be just fine it'll be empty here so  let's do that inside vba let's also add on to that   macro bringing those formulas down so let's get on  that do that right now so inside back inside our   macro here i'm going to bring down those formulas  so we're going to call this adding those formulas   for both the barcode and the totals so we can  do that so we're going to focus on the invoice   worksheet now so what i'm going to do is this was  based on the items so we're done with the item so   i'm going to go up and now we're going to focus  back on that invoice worksheet so with inventory   adjustment worksheet here work sheet now we're  back on that okay so now back on that what do i   want to do i want to determine what's the last row  of data now we know the last row of data is going   to be basically this plus 4. that's our last row  i just want to redefine that last row is equal to   the last row plus 4. okay last row because we're  going to work with that last row once i have that   again we're going to run bring those formulas down  so how do we do that so dot range all we do is j   right that's our first one 7 that's where it's  located here through m in the last results row   formula that's our first row data through m and  the last row not that value want formula dot   formula is equal to what equals to basically  it is j five j five that's what is dot range j   five that's our foremost through m although it's  really j and m j5 through m5 that's fine right   dot formula so this is one line of code it's going  to bring down all those formulas automatically to   that great so now we have that we've brought down  let's go ahead and run that macro again we can   also clear the filter so we can see let's go ahead  and clear the filter out simply be delete this and   then what we're going to do is we're going to  assign it to this this new adjustment so i'm   going to right click here assign the macro to the  macro we just created called inventory adjustment   load actually we're going to do load items that's  the one i want because we're loading all the items   and we're going to click here so there we go so  now we've got all the barcodes that have loaded   here and we can just double check it to make sure  it says e9 that's what i want e9 plus the asterisk   so we've added that also on here we've got now  we want to add the pictures and also when they   add the quantity we're going to say okay but we  do need to make an adjustment here because we   want to make sure that in this formula i don't  want to load those adjustments notice it's zero   i only want to put a total here if this contains  a value if it's not so all we need to do is just   make an adjustment if right we can use this row  equals empty then show empty right so we don't   want to show anything unless they make an error  and then we can just end parentheses there at   the very end of the last row okay so now we've got  that so now as soon as i click new adjustment it   clears out those as soon as i do make a change to  that then it shows and that's really what i want   essentially so now we have all that information  there and so only when they make changes to the   actual quantity does the value adjustments up so  everything but now we just want to add in those   pictures okay saving our work now we're going  to add in those pictures it's relatively easy so   let's do that focus on that so inside here we're  going to add the pictures in add item pictures   okay so what we want to do is we want to define  the folder of that so how we can do it but what   we can do it might be even easier i may add those  pictures in right inside another macro right so   because i want those pictures to be added in when  i add a few things let's go ahead and run a brand   new macro i've got an inventory adjustment add  picture so we're going to use this macro and i'm   going to copy this macro which we're going to fill  in right now i'm going to place it directly right   in here so this macro will run and it is this  macro in which we will add those pictures in so   how do we do that well again we're going to focus  on that inventory worksheet here so with inventory   worksheet making sure we have right and then what  we're going to do is i want to define that picture   folder but first i want to make sure that we have  the last row right i want to know the last row so   the last row is basically going to be a a there's  our a column a getting our last row of data so   we're going to define that our first last row of  data because we need to loop through all the items   i want to define that picture folder that's the  stream variable and where's that picture folder   going to be located it's in p4 remember we place  that path inside p4 so if we take a look inside   right here that full path is our picture folder  the only thing we need to do is add a backslash   onto that so we're going to say that's where we're  located so let's put that inside a variable here   so picture folder equals dot range p4 so putting  that p4 and then i also want to add that slash   onto that so and the backslash so now we've got  that and now what i want to do is we're going to   run a loop so we're going to run a loop for each  item there four item rows equal to we're starting   at row seven seven to the last row next item  closing our loop so now that we have that we can   run it so what i want to do is i want to set that  picture path that picture path is based on what   remember our picture name is in column b that's  where our picture name is so the combination   of that picture name that we brought in with the  folder is going to be a full picture path so we   can use that to bring in our pictures so here  we go we can write the code for that so for the   last interval we're going to set that picture path  that picture path is going to be set here picture   path which is the string variables equal to the  picture folder we've already added the backslash   and dot range b and the item row and the item row  dot value this is going to be the full picture   path we do want to make sure it is accurate so  we do need to run a check if picture we can use   directory directory picture path vb directory and  then we're going to check is it empty equals empty   or maybe the variable itself or maybe it's just  something like that so we need to check to make   sure it's empty so or the picture path let's do  this let's do in case they do the picture folder   or picture folder equals only the backslash  that means the user has not said it when you   get this file it'll be blank so the picture  folder could be then what we're going to do   is we're just going to exit we're going to  go to no picture let's say go to no picture   and then i'm going to put that down here right we  want to skip to the item right no picture so we   can go to the next one okay but assuming that  we do have a correct path i do want to insert   that picture so we can do that here dot pictures  dot insert and where we're going to insert that   picture that picture is going to be that picture  path so we're going to enter that picture path   and then we can what i want to do is assign a name  remember that name has to be very specific so i   want it unique and i want it specific so we can  remove them all so that picture path is going to   be equal to item pick and then we'll give it some  unique value such as the item row and the item row   then it's unique unique picture name once i have  that what i want to do is i can work with that and   place that very specifically so i'm going to copy  this and focus just on that one so now with dot   shapes that picture here we can work so now what  i want to set the left position so that's the   left position let's do left is equal to the  position of that where is it equal to the invoice   we need to call out that sheet again because we're  now inside another width dot range where is it   going to be i want to put it on column k right  we want to put that picture directly in column k   let's take a look inside that to confirm that what  column we want to place it on directly in column k   okay so we know the column we're going to place it  on so that's going to be the left based on that k   and what k and the item row dot left but i don't  want it right on the left i'm going to add let's   say some pixels let's say plus 10 pixels so we're  going to set the set left position okay i also   want to set the top position the top is going to  be very very similar so we can just copy this and   then make the update it can equal to the same cell  but the top position of that cell so we're going   to do top position and that's going to set the top  position okay also what i want to do is i want to   make sure we lock the aspect ratio of that right  now so i'm going to do lock aspect ratio equals   and then true right we want to lock it sorry it's  a little bit low scroll up lock the aspect ratio   equal to so it's going to lock the aspect ratio  right we don't want it contorted lock aspect ratio   and i also want to set a specific height i want  to set a limited height we can't have it too high   i can't have it higher than the row height so to  do that we just set the limit to that to basically   the row height okay so let's do that right now  so the dot height will do dot height is equal to   we're going to base it on the same idea right that  invoice worksheet we don't need the double equal   there same but this time that same row we're going  to base it on the height of that row dot height   but i want a little bit less than the height of  that minus 2. so i want that entire height of the   picture slightly less than the height of that row  set picture height very very good so we're just   going to loop through this and go end with and  then if there's no picture we'll go ahead and then   we're going to loop through all the items okay i  like that there we go that looks good so remember   we're running this macro right here so as we save  this we're going to automatically add a picture   let's go ahead and save our work always before we  run the macro then what we'll do is go in here and   we're going to run that macro one more time and  we're gonna see how those pictures come out take a   look all right the pictures look good they're all  here they look properly placed properly sized all   right i like it very good okay so now we've placed  it so now we've things are really starting to pick   up i don't quite want these in black we can hide  those if you can of course you can hide them using   our trick or we can just make we'll make them a  lower color so we know that it shouldn't be like   this let's do something like that color so we've  got our database row everything's looking good   we've got our data set now we're making sure let's  go ahead and sort by let's do sort by quantity in   stock and then we'll click new adjustment so now  everything's sorted by stock that's looking really   good but what i would like to do is when i change  this i want to run this macro when i change this   i want to run this macro like if i say be at  bath i want that macro to run automatically when   i make a change either to f4 or i want to make  a change either to i4 but i just want to make   sure that those changes are not blank and that's  in the worksheet change event so let's go ahead   and go inside that individual worksheet this  invoice worksheet and we're going to write   some code there just a little bit worksheet based  on the worksheet change event all right it's   based on worksheet change so we don't need the  selection change so what type of change so again   if not intersection let's do that if not  intersection so we're focused then what do   we want to do so let's set the cell what cell  is that of course the first one we're going to   focus on is our filter which is located on f4  so that's the one i want to focus on here so   f4 we also want to make sure that f4 is not blank  so nothing in range f4 dot value does not equal   empty then what do i want to do then i want to  run this particular code i want to run this macro   exactly when we run that filter so i'm going to  copy that and then what i'm going to do is go   back in here and that's the macro we want to then  run the macro okay i also want to do something i'm   going to copy this because i want to do something  very similar for the sort i'm just going to change   the ranges on that the sort of course is based  on i-4 so if the user makes the change to i4   and i4 is not blank then we want to go ahead and  run that so now when we change that to item name   it's automatically going to run we've already  got the filter in here right so that already   automatically filtered which i want that if we  do item cost right i want to know and let's do   greater than 50 then we can sort the item  costs greater than 50 that looks correct   very good we got a 55 here but nothing under 50.  all right so we've got that and also want to let's   set up our clear filter i want to make sure that  we can clear those filters clearing filters should   be relatively easy all i want to do is clear the  contents of f4 through g4 and i want to clear the   contents of yeah just this not declare and then  i'm just going to run that filter so clearing the   filter is going to be very very easy so let's  write that up right now that clear filter and   then we'll assign that macro so we're going to  go out of this screen and go back into the next   one which is clear the filters so let's go ahead  we've added pictures clearing the filters next   and we'll bring this up a little bit relatively  easy this one's going to be super easy so we can   get rid of the extra spaces here don't forget if  you like this code i have brand new pdf code books   are going to be available very soon for all 200 of  my workbooks so pdf code books if you don't know   they're a great way to display this code so you  can display the code have the live links inside   have an index have a table of contents you're  really great okay good so we've got that now we're   going to end let's change that to end with make  sure that doesn't all right so continuing on with   the clear filters so we're going to focus again on  that particular width sheet so invoice worksheet   dot range all we need to do is f for it's a merge  cell through g4 dot clear contents then all we   need to do is just run that macro right this one  right here actually this i've got that copied and   pasted but that we don't need so this is the macro  that i want to run so it's pretty much just two   lines of code very very simple it is this macro  that we're going to assign with that clear filter   button so that's all it does it's very simple once  we set up good formulas we can just copy that okay   bringing that down here then what we need to do  is right click this assign the macro paste that in   and click ok clicking ok clears that filter now  the clear filters now you assume we're showing   all types of items here and we're showing the sort  but the sort still works here we can sort by part   number we can sort by sales description and i  really like that okay very good very good so now   all we need to do is we just need to then let's go  ahead and do that add new that was the first macro   that we skipped so let's go back to that first and  add new when i add a new macro i want to make sure   that we are adding that so let's go ahead and  clear that up to make sure that we're adding the   new one because it's a little bit different so  we're going to clear it so all the way up here   we've got inventory we're just going to add the  new one so want to make sure that we're adding   clearing it all in so new adjustment is going  to be here and then we'll reassign that macro   to this button here so we can reassign it okay so  i'll show you exactly how to do it all we're going   to do is simply make sure we're clearing out the  right one so with inventory work sheet and then i   want to clear out somewhere first of all i want to  give the user the option just like we did here i   want to make sure that we're giving you the option  and i'm going to copy all this we're going to put   that in in new worksheets going to add that  it's going to add additional features though   ok so we we probably don't need this on the add  new we don't need that one but i do want to clear   out the contest but i want to clear out a few  more i want to clear out all the selected the   id row whichever one id i want to make sure and  then also i want to make sure f4 through g4 is   cleared out f4 through g4 that's our filter and  i also want to make sure that we're clearing out   everything from a7 okay so basically remember b3  is the id that id it's very important right that   we clear that id out it's a brand new one and also  we want to clear out any filters that go and now   what we want to do is also run the macro to load  the items and then we'll assign that macro so the   last thing is here inventory load items here this  will just add this in here inventory load items   copy this here place that directly down here all  right there we go so now we've added this so now   but i also want to do one other thing i also just  want to make sure that we're going to clear out   all the picture shapes so i'm just going to copy  this here to make sure that they're all cleared   out clearing out those picture shapes can assure  that i'll show that where and also i want to set   changes to false so dial range b2 that's where we  have changes right b2 dot value equals false very   good all right so we got making sure that they're  false all right so let's take a look at that now   let's do before we forget remember when a user  makes a change to anything on l7 all the way down   i want to make sure to change b2 to true so let's  write that up right now inside the change event so   back into the invoice worksheet here's our change  events right now so we're going to if any section   here if not target range intersection is nothing  now what i want to do l7 all the way on down set b   to true so here we go nothing the l7 all  the way to let's say a large row l7 through   l999 now think and and let's do this and target  dot count large is less than two just in case   they're making changes to one cell lesson two  then right range b dot value equals true okay   so now right it's false when they make a change to  the actual quantity i want to make sure the b to   goes to true now when they click new adjustment we  want this pop it says changes have been made are   you sure you want to save those changes no right  so are you sure you want to save i think i should   be vbs right shouldn't that be if you want to save  those changes yes no so let's update that right if   you are you sure you want to clear out the changes  without saving it's a little bit ambiguous all   right so it's are you sure let's add let's update  the text here put in the proper spelling here and   here so once we have that are you sure you want  to clear these changes without saving if they say   yes right they'll continue on if they say no we're  exiting this up okay that should be sufficient   all right so we've got that it's working just  right now what i want to do is add that new macro   right to this error because we're already loading  in assign a macro this is going to be for the add   new one right here so now it's going to clear  it all out now it's going to set this to true   so we're going to say yes now we're going to  say yes we do want to clear everything out and   it's going to clear everything out clearing all  those pictures and it's going to reload everything   ok perfect i like that just the way it is it's  looking really good we just have a few more macros   to add so we're moving really well on this okay so  we've got the add new what i want to do is i want   to set i want to be able to save it right when the  user makes changes right when they make changes i   want to be able to save those changes all to the  database row i want to know the adjustment i want   to be able to brand new adjustment the date the  total amount we didn't add total amount we do   need to add in that so all we need to do is simply  sum the total amount we're going to start it here   and go all the way down to just a large row here  that should be fine all the way through let's just   say 9999 that should be good okay so now we've  got the sum the total number of adjustments   located in m4 so i'm going to save that also  inside that and then each individual item in   this case would be four different items those  items i want to save in individual rows here so   we're going to run that macro now we're going  to create that macro now and then we'll run it   okay so that is simply called the invoice item  save so inventory adjustment save that's the   macro i want to right now saving those individual  ones so how do we do that so before saving though   i want to make sure that the users actually  entered some values in here right if they've   brand new adjustment and they try to save that if  they try to save let's go ahead and new adjustment   and yes we do want it we don't want to save those  changes so if they haven't made any changes here   at all right i want to let the user notice that  they can't save it until they've actually made   changes to some of the quantities so let's go  ahead and check that the first part of the macro   so with indoor work sheet then what do we want to  do with this worksheet what i want to do is i want   to say using check to see if there's anything  filled in that range so we're going to use if   we use application worksheet function and  i'm going to use the count a function count a   because i want to count the strings and what  range do i want to do i want to use l7 dot   range l7 all the way through let's just say l  and then a large row 99 right so what i want   to do is i want to count all the text in there  if that's less than one less than one cell then   let the user know message box please make at  least one inventory adjustment before saving   okay exit the sub right if they haven't made a  change there's nothing we can do okay continuing   on what i want to know is i want to know the  difference is it a new adjustment or is it   an existing adjustment that way give the user the  ability to make changes to an existing adjustment   so we know our adjustment row here on b4 is our  adjustment row if b4 is blank we know it's an   existing if b4 is not blank we know it is we  know it's an existing otherwise it is not so   we're going to use b4 to base that up all right so  that's all we need to do check so if dot range b4   dot value equals empty then we know we have to add  in it's a new one else it's an existing so else   existing and then before empty this would be new  so this is going to be new adjustment if it's a   new adjustment i need to assign a brand new id  for that so new adjustment in this case what   we'll do is where we're going to get that new  idea we're going to get it directly from b5 but   first let's set a row so the adjustment row is  going to be based on adjustment this is our table   here dot range then a 999 and excel up dot row  plus one so it's gonna first available row first   available row that's our first row so now we  know the row is so now what i want to do is   i wanna take our next id and place it directly  inside b3 our next id we know we already got our   next id is located in b5 so i'm going to take  that i'm going to place it directly inside b3   so we can do that just inside the code here  dot range b3 a value equals dot range b5 so   that sets the new id b5 set new adjustment id  also what i want to do is i want to place that   id i want to place it directly inside a so i'm  going to copy that so a and the adjustment row dot value is equal to whatever is  located in b5 right equals dot range b5   b5 all right new adjustment id so that places it  directly inside it that's all we need to do if it   is a brand new one but what if it's an existing  one if it's an existing one all we need to do is   simply set the row based on what's in b4 so we  can copy this and our adjustment row is equal to   whatever's in b4 existing adjustment row all right  so now everything else is based on uh whether it   is new or existing so all we need to do is copy  this and just update that so that means so what do   we need to do so we need to what do we need inside  here so our adjustment here is we're going to have   our id here which is already placed our date and  our amount so that's all we need to do is just the   date and the amount in b and c so we can put that  in right now so let's add those in for b and c   so this is b equals we'll add that equals the date  right and this is set current date and that's not   going to go in a it's going to go in b and then  in c what we're going to be doing is we're going   to be just placing that amount and that amount  is located right inside cell it's going to be b   m4 so that's the total amount m4 right let me  take a double check that m 4 right here that's   way a little bit off the screen m4 is where we  want that adjusted total value i want to know   what that's going in okay so we can bring that  in here so that's equals m4 and that's going   to go directly into column c and that's the total  adjusted value total adjusted value okay so that's   it for the main database that's all we can now we  want to do is want to focus on those items right   so now we can do so we want to focus all the items  what i mean by that i mean every item they've made   a change in here i want to save those individual  items inside this adjustment item so i want to get   the last row but how do i know if they've not been  there before what we're going to do is we're going   to assign a row here if they've already been saved  that row is going to show up right here so i'm   going to check i'm going to loop through all the  items i'm going to check is there a row is there   a change here if there's a change is there a row  associated with it here and so we're going to add   all those items based on the last row so we need  to loop all the way into the last row so let's go   ahead and set that last row right here so the last  row is going to be equal to we'll use column a   it's fine because we have items to call it i want  to know the last row of data so let's go ahead   and put a comment in here add adjusted items only  those items that have been adjusted but we need to   loop through from seven all the way to the last  row so we're going to start that loop right now   four the item row is equal to seven to the last  row close our loop next item row okay so once we   have our item row then what i want to do is i want  to determine has there been a change right column   l is our change so if only if dot range l that's  where we the the user will put in their invert   their item adjustment and the item row dot  value does not equal empty does not equal   empty then we need to we know they've made a  change then so end if so then we'll call this item   has item quantity has been adjusted okay so we  know it's been adjusted but what we don't know   yet is has it been previously adjusted or is it  the current right we don't know has is there a   database assigned to it right if i load in  these items i know that this row is going to   come directly over into this so i know it's been  previously saved if it's been previously saved i   need to set the row based on whatever the row is  here if it has not been previously saved we need   to assign a brand new row so i want to check in a  row in row in the current row and whatever column   o is i want to see if there's a number there or  not so that's the next check we're going to make   i'm going to use that with if i also want to know  what the item database row is so we'll set the   item database row as to whatever's an item because  that's going to be there no matter what database   row so that's let's not get confused here remember  this is the item database row for our original   items on based on their original row here we have  an adjustment row when rows are when they're made   adjustments are made for a specific item it's  safe here so we have two different rows that   we're associating with but i do want to put this  first one inside a variable here this database row   in n i want to put that in a variable so we're  going to take care of that that's going to be the   item database row and it's going to come directly  from m equals n and the item row and the item   rows our database item database row call this  item database row okay so once we have that set   now what we can want to do is i want to check  to make sure to see if it's updated or not   and i need to know that item database because  i've got to update the quantity right if i've   changed a specific quantity i need to update the  agile quantity if this quantity was originally   four and now it's only two i need to look for  that original row i need to find that original   quantity that quantity on hand in stock here  which is in column m and i need to update that   i need to put whatever the new quantity is and  that's column m so we need to update that inside   that so how do we do that and that's going to make  sure that there's an item database associated with   that just to make sure and we're going to do that  so as long as the item database row is not zero   if the item database row does not equal zero then  we're going to make the update so items.range   m is where they'll clone it and the item  date of show item database row dot value   equals dot range and spelled value right  value equals dot range where is it coming   from l that's the new quantity and the item  row and the item row set that new quantity   update new quantity on hand okay great so we've  updated that now what i want to do is i want to   check out so now we're going to do okay let's fix  that does not equal zero this should be zero okay   then what i want to do is now i want to check now  we're going to check uh column o here so now we're   going to run that check to see if it's been saved  before so let's go ahead and do that if dot range   o and the item row and the item row dot value does  not equal zero or does not equal empty either way   it's not equal empty then we know it's been saved  before so or we could just go equals empty equals   empty and then else will do not okay so then it's  a new adjustment new let's call it new adjustment   item then okay so end if and then else right it's  an existing else existing adjustment item so if   it's a new adjustment item we need to get that  new row we need to add a few things so if it's   a new one so that adjustment database row is equal  to the first available row but that's not based on   the current sheet it's based on the adjusted items  adjusted items sheet that's the sheet we want to   get the first available row so we're going to  add one here first available row first available   row okay so now we've got the first available  row but what i want to do is i want to set that   specific adjustment id in column a so again so  let's do that adjusting items dot range a and we   know the row now adjusted let's call this adjusted  database row dot value right what do i want to put   in column a well i want to put here in column  a i want to put that id and that id is going   to come directly from b3 right here whatever's in  b3 i want to put that id there so we can do that   inside the code because i need to separate when  i'm loading it i need to load it based on the id   equals dot range b3 set adjustment id all right so  now we've set the adjustment id we've got that all   set up but i also want to do is i want to make  sure that we're going we've got our brand new   database row so i'm going to take that database  row and i'm going to place it directly inside o   because now it's blank before but now we're going  to add it in so we're going to put that directly   in oh so we can set that database row so we can do  that all we need to do is just copy this because   we know it's right here and we're going to just  place that row in there so this is now going to   equal that adjusted let's say just a database row  set database row okay very good so we've set that   database row right there in column o that is all  we need to do for a brand new database but for   existing all we need to do is extract that row  this row from column o right we have it here so   here i've already copied it adjusted database row  is equal to this right here existing database row   so now we have the row so now we have both that  so all we need to do now is for the existing is   add the date id ash quantity row so this is going  to be done actually the row could probably be done   for only new ones so let's set that up because we  only need to set the row once so we don't need to   do that more than once so let's update let's do  that inside here right inside here we're going   to set that up because it's only for new ones  so i'm going to copy this here and right at the   let's put it right here e is going to equal to  the row we only need to do that the first time   equals put in a formula here so we can set  that row so we know the row and then just use   the parentheses and then equals and then row and  then double parentheses like that quotation marks   and then we have row and then that's going to set  the row formula formula for the row row number   okay so we've set the formula for the row number  in column e and we only need to do that for new   ones very good so for existing ones all we need  to do is start off on b and set that date so i'm   going to copy that and we're going to go into b so  b is going to take on the current date equals date   set current c is going to take on the item id so  c here is going to take on that item id i want to   know the item id where is that going to come from  it's going to come from a right equals dot range   a and the item row so i want to put remember i  want to put that item id i want to save this is   going to be item id that item id is going to come  directly from right here in a so we've got that   set up all right so once it's so once that's set  up in a let's just add that in here a and a row   add item id and i want to know the quantity well  that's going to come from l right so we can just   copy this and then adjust the columns so the so  inside column d we're going to put the quantity   what is the actual change that's going to be  an l and that's the adjusted quantity adjusted   quantity all right the last thing is that's it  actually because we've already added e up here   so we don't need to add e that's it that's all we  need to do to add the items and what we're going   to do is just clear out the additional spaces we  don't need them and then that completes our loop   and then that completes our end with okay and then  we'll run that code always going to save before   running the code and i'm just going to check to  run that code but we don't have right we're going   to save that as we want to make sure it's a new  transaction we've got some quantities here that's   correct let's add this let's change that right  and then we're going to let's take a look this   is going to be i'm going to delete this right  because i want it to be a brand new one right i   want it to be brand new and we have no it's going  to be brand new we have no saved databases and we   want to assign this to a brand new so it's going  to be this macro we just created assign macro and   that's the one we're going to do adjustments save  so we're going to click ok save in our work once   again and then click save and it's used okay so  now we see that item database rows 16 17 and 18.   we'll take a look at that 16 17 and 18. our  adjustment id is four that's correct we've got the   rows here that's saved it inside we've got a brand  new one here we've got the amount that got saved   here so everything looks really good it got saved  okay great but what i want to do now is i want   to load that up right i just saved it right we've  got three different adjustments therefore actually   so i want to be able to load these using our  navigation if we're at four and we go previous i'm   going to load three two or one so i want to write  a macro that's going to load in whatever's so what   that means is that if let's say let's say 2 is  here i want to load that one and i want to load   only those items that were adjusted and we'll load  it in here so how are we going to do that well   just a little bit of a code so but what i want to  do here is i want to load all those items in we   notice we only have information this information  we only have item id but i want to load all that   item information right so if i'm going to run an  advanced filter and i want that criteria to be   based on whatever's inventory b3 i want to load  all those for b2 would be here right i want to   load but i only have the item id here i don't have  all the item information which is okay but what i   can do is i can extract that item information  from a from our item database here so we have   it so what we'll do is we'll create an advanced  filter we'll have the results the item id come   here then all the item id information we need we  have we need a picture we need our blank right we   need the blank it's got to be in the same order so  we have the id we have a picture we have a blank   column just like we do here we have starting out  here the id id the picture and a blank column then   we have the name part so i want exactly the order  same order but this time we're going to extract   data from here using formulas so that's just what  we did in here blank then we have the item name   the part number the category so what i want to do  is i want to write a formula here here here always   and i want to write a formula based on the id  here so whatever id is here i want to extract   the the picture or i want to extract the item name  and if you remember we have a named range called   item data right item data so what i want to do is  i want to extract based on the item id the item   id located in i4 or j4 and i'm going to extract it  so we're going to extract it and i want what's in   column 14. well what's in column 14 of our items  well that's the picture if we click here and go   column right it's 14 right so if i want to extract  that's why i don't use these types of large datas   because i don't know what's in 14 right i like  to use named ranges for everything but it gets a   bit much sometimes so right because if i look at  this formula that's why i don't do it too often   if i look at this formula and i don't know exactly  what's in 14 but if i had a named range only for   the item picture then i would show something like  item pick picture and then you would know exactly   what we would index the item picture and then we  would change this to one right so that's what we   would do so that's kind of then we would know  we're indexing the item picture we would know   exactly what we're indexing and it would be very  easy to to see so but that's but in this case we   have so much data let's escape out of there we  have so much data we're just going to use column   numbers in this case so column numbers 14 is for  picture item name is column number two uh four is   for part number and so on and so forth so once  i have these formulas deformers are always here   once the results come here and the results so we  only our results are only item id wherever there's   headers the actual quantity so our results  from advanced advanced filter will come here   and we'll come here so only three columns for  our advanced filters then what we want to do is   once we know the last row we can bring down those  formulas for all the rows so bring it down those   forms then we accept all the data exactly where we  want it the only thing we need to make sure when   we run our advanced filter we must include the  blank header right it's got to go all the way to   f because our results contain blanks so that's  very important okay so let's write that code   right now so we can see exactly how it's going  to work so scrolling down here we have inventory   adjustment load that's the macro we're going to  be running we're going to be loading first thing   we need to make sure of is that we have a row to  load right we have to it's previously saved so we   need to make certain that b4 contains roan is not  blank so if it is we need if it's blank we need to   let the user know so with inventory worksheet if  dot range b4 dot value equals empty then and you   can double quotes the same thing then message  box please make sure to now though it shouldn't   happen in this navigate to a correct adjustment  okay so that's we'll just leave that leave it   like that that's sufficient enough and then exit  the sub okay but of course we should have a value   in there so we can tie that to a variable if we  want we also want to make sure again i want to   make sure that if b2 is true i want to make sure  that we're not loading so what i'm going to do   again i'm just going to copy this here because  if b2 is true i want to make sure that we're not   loading in case they want to save their changes  so i'm just going to copy that b2 equals true   change or save then exit the sub all right so  that's good so be true continuing on now what we   want to do is we want to clear the contents right  we're loading something up so i want to clear   everything out just like we did in the add new we  could simply run the add new but add new of course   is going to clear out b3 which we don't want to  do so what we can do here is just copy this here   and then paste that up so we also want to make  sure b2 is false here copy this but making sure   that b3 is not included in that right we're going  to have to exclude that b3 is very important   b3 is going to dictate what our row is so clearing  that out clearing everything but b3 very important   right b3 is our idea we cannot clear that out  right if i clear that out then so there goes   b4 so that's important we want to keep that in  there so moving on so we've cleared everything   out now it's set between now what i want to do is  i want to loop i want to run our advanced filter   but to do that we need to get the last row okay so  we're pretty much done for just a bit for with our   inventory worksheet now we want to put the focus  back on our adjustment items right so that's the   sheet we're going to focus on with adjusted items  this we're going to get the last row so we can run   so the last row of this is simply equal to  based on that so it's the last row of data   so if the last row of data is less than three then  we know to exit the sub out okay assuming that we   have value what we want to do is i want to then  run our advanced filter so to do that we can do   advanced filter we're going to run that advanced  filter it's going to be basic and remember i said   we have to include that blank column we're going  to go all the way to f a2 all the way through f   our criteria is going to be h2 through h3 so let's  write that up right now so our criteria lasso is   going to go to f for our original data we're going  to have our criteria h2 through h3 here then what   we want our original data right it's going to have  to come all the way from j3 we're focused on three   this time all the way through t3 j3 through t3 is  where we want those results to appear so j3 here   all the way through t3 okay and we want unique  to be true so j3 through t3 now that we have that   come up what i want to do is determine the last  results row so the last results row is equal to of   course it's going to be based on column j so we're  going to set this to column j i want to get the   last results row if the last results row of course  is less than 4 in this case we want to make sure   that we're going to exit the side that means there  would be no data so last is also less than 4.   all right assuming that we have data what i want  to do is then i want to bring down those formulas   right so all we've done is bring this in and these  and these three columns in but now what i want to   do is bring down the formulas so we're going  to base it on our last results row so we could   write just one line of code to do just that okay  so how it's going to be so we're going to go dot   range in this case we're going to use column k as  our first column k 4 through all the way through   r is the last one then what we want to do is and  the last results row and the last result rows not   value its formula all right dot formula equals  where's all our formulas located k through r2   dot range k2 through r2 that's again not value  but formulas formula we're bringing down those   formulas going to bring down the data we want so  now that we have the formula now we brought over   all the formula brilliant call this bring over  item formulas copy down item formulas so we got   the item information so that's going to bring  all the items inside the results once we have   that we want to make sure that we're going  to bring it over into our worksheet right so   here i want to bring it all the way but first  thing what i want to do is i want to make sure   that we're bringing everything over bring in those  item details so i'm going to bring in everything   all the way from here all the way to let's say  r all the way here so what is this one here   this is our quantity in stock so i'm  going to bring it all the way from the id   to the quantity in stock and i'm going to bring  it directly inside here starting in column a all   the way through column i so let's write that up  inside the code now of course we have to call out   that specific sheet so the inventory worksheet dot  range a7 is our first row a7 all the way through i   and the last result last result row remember  this one we're starting at four our results   start on four here our results start on seven  here so we need to add three so plus three on   that that value is equal to dot range where's it  coming from let's double check it's coming from   all the way from j4 here all the way through r in  the last result row so we're going to bring that   over there okay so equals j4 through r and the  last result row so that's going to bring over all   the item details once we have that now what i want  to do is again i want to bring down this barcode   formulas just like we did before and i want to  also bring the actual quantity so i want to bring   those formulas just as we did before remember  we brought those formulas all the way over here   so let's write that up again so the invoice  worksheet dot range it's going to be from j7   through mj7 through m and the last result again  last result row plus three dot formula equals dot   range where's located in row five j five through  m5 j5 through m5 bringing down those formulas   we need to bring those forms dot formula okay  and those are for remember the barcode formula   and also the total formula so we need that so  that's a bring over bar code and value adjustment   formulas okay so we have both of those so once  we write all we did here in that line of code was   we simply copied the formula our hidden formula  that's located right here inside j4 and our hidden   formula that's located right here so bringing  those over and bringing those down that's going   to put our barcode and any adjustments now all we  need to do is bring in the other information that   we have we just have a little bit this database  row we need to bring in the database row and i   need to bring in the item rom so let's continue on  with the code so continuing on so now we're going   to focus on l7 i also want to bring in the actual  quantity right we've only brought in so far right   we skipped this we only brought in so we're going  to bring in the actual quantity and the row so   that's going to be l through s bringing it from ls  let's go ahead and we can just copy this over here   and then make the adjustments inside the code so  this time we're bringing over the actual quantity   so we're going to call this the actual quantity  and write the original that located quantity in   stock here right i want to bring in the actual  quantity from s that's going to come from s   right here and s and s okay so s4 all the way s  and it's going to come directly into l right l   is our column inside our worksheet there that's  where we want the actual quantity to appear so   l and then lastly what we want to do is we want  the adjustment item row so i'm going to copy that   paste that in once more this one's going to be  our adjustment item row adjustment item raw so i'm   going to clear that out put in adjustment item row  and that of course is going to come in here i want   that row this and t i want to place that directly  inside here inside o okay so let's take a look at   that right now inside there so gonna put it in  from o here through o in the last results row   all we need to do is just update that there okay  good so that's gonna come put directly from column   t right coming from t that's it that's all we need  to do there we go so now all we need to do is run   the macro i want to add those pictures again right  we want to add just for the existing pictures   that's why we put adding pictures into separate  macros so i'm going to copy this adding it's going   to determine the last row of just resist and  i want to put those pictures in right here so   adding those running the macro to add the pictures  in it's relatively self-explanatory add pictures   okay so we've run the macro to add the pictures  that's it for that okay so if i decide i'm going   to load one let's check again saving our work i'm  going to we'll enter this manually this time but   it'll be automatic with just the next two macros  two has been previously saved right we know 2 is   here and we know we've got some items associated  with the id 2 right these items here so all i need   to do is just run this macro and i'll run it just  by running the play and then we'll see if there's   any issues and then we'll fix those and it says  changes have been made do we want to save those   changes no we don't want we don't are you sure you  want to click clear these changes without saving   yes i do and then we'll run it okay good so that's  exactly what we've done we've run the barcode here   i think we need to update this here that didn't  come over so let's take a look at this so the   formula here is we've got to update that all right  i see a few things wrong our formula obviously   didn't come over let's fix that formula i think  i know the issue okay so the formula comes from   invoice worksheet not our data so we need to  update that our formula is coming directly from   here okay so we'll run that again and take a look  at that okay that sets our formula these pictures   shouldn't be showing up so i want to know why the  pictures show up pictures should only show up if   we're looping through this it should only come to  the last row of nine so let's check the last row   on the pictures something's quite wrong with that  on that last row setting up that last row so if we   take a look at adding pictures and we look at the  last results row it's a right invoice where she's   adding the pictures probably this should be last  row right correct right because we're focused on   looping through the last row this should be last  row not last results row okay that looks good so   now we're going to run that again and obviously  it's going to duplicate those pictures if we don't   clear them all out so that's why notice look at  this so why did this happen because these pictures   were already here disney doesn't duplicate if  i just ran the macro known but when i click two   if i double click here we just want to make sure  all the pictures are deleted right if there's a   duplicate picture notice picture eight and picture  eight there's two duplicate pictures so it didn't   cite them and that's because our macro doesn't  clear it out but that's okay if we new click new   adjustment it's gonna clear out all the pictures  which is fine if i go ahead and put the previous   adjustment in here and then i run that macro again  it'll be just fine so now what we're going to do   is going to run that macro again the macro we  just created and that's going to be the load the   invoice adjustment so this macro here running that  macro taking a look at this so now we've got three   we've got our last row correct with the pictures  we've got our formulas in here our from is this   didn't change in quantity so there's no value  okay i like that it's loading grid but we don't   want to run the macro i want to run that macro  when they click previous or next that's when i   want to load in so basically when they click  previous what i want to do is i just want to   reduce whatever the adjustment id is here and load  that one so if they go from two they go to one   or if they go to next they go from two to three  assuming that they're values so it is these two   buttons that last one we're going to write that's  actually going to load that previous adjustment so   let's write in that right now two very very easy  macros so previous and next are the last two   micros oh then we of course have to print it but  that's a very one so we're going to focus on that   one the previous one first thing what i want to do  is i want to get the adjustment id whatever's in   that i'm going to set that with of course with  invoice work sheet okay and also want to set that   adjustment id so the adjustment id is going to be  equal to dot range whatever's in b3 i'm going to   put that into a variable it's a long variable  adjustment id adjustment id okay once we have   that what i want to do is i want to check if  it's zero if the adjustment id zero why would   it be zero if they're adding a new one it could  be zero id equals zero then what do i want to do   then well i want to go to the last one if they're  going to previous right i want to go to the last   one what does that mean okay so let's say they  do a new adjustment there's nothing here in b3   when they click previous what do we want to happen  well i want to go to the last one created what i   really want to do is i want to go to this one  right here i want to go to four right that's   what i want so let's do that let's determine what  it is how do we know what the last one is created   well i can look here i know the next idea is five  so that means the previous one is four so that's   how we can do it so that's what we're going to do  inside the code we do need to run a couple checks   okay so let's just say no no current id current  id okay so when we have that what i want to do   is i want to make sure that b5 doesn't equal  one what would happen if b5 is one that means   we don't have any adjustments at all there's  none here none right so that way they can't   navigate if they haven't created any previous  adjustments they can't navigate to anything so   i need to let the user know so we're going to  check it's b51 if it is let the user know that   they can't navigate to anything because they  haven't created it if that range b5 dot value   equals one then okay what do we want to do and  if all right first end if and then i want to   let you know let's put a little note no saved  adjustments so we're going to let the user know   with a message box not the no message box please  save any adjustments first before navigating to   previously saved right that that helps the user  previously saved helps the user decide what to   do exit the sub nothing we can do if they haven't  saved anything yet okay but what if b5 is not one   in that case what i want to do is i want to go to  whatever is the previous one so how do we do that   so dot range b3 we're going to set that b3  it's equal to dot range b5 whatever is the   latest one minus one minus one set the adjustment  id to the last one created to the last one created   all right great so that's it so then what i  want to do is simply run this macro right here   that we just created this is the macro we're  going to run and then i want to then that's it   that we're going to exit the sub exit the sub  there's nothing else to do there so we've run   it and we're going to exit this up okay good then  we're already done so that's it for that that's   only if it's zero but what if it's not zero what  if it's one that means they're at the beginning if   the adjustment id equals one then let the user  know message box you are already at the first   adjustment created right they can't go be beyond  that right created okay and then exit the sub if   it's not one it's very simple all we need  to do again is just set b3 here we can copy   this b3 is going to be able to what whatever  b3 is copy that b3 is equal to no let's not   actually we can paste that in there we're going to  need that in a moment but i want to copy that here   and i want to paste that right at b3 it's equal to  whatever b3 is minus 1 set previous adjustment id   now we need to load it and that's good so that's  end with all right let's copy that and let's   add that to that we don't need to copy that saving  our work it is this the macro here right click   assign the macro and then what we're going to do  is this is the previous one so previous adjustment   so if we're currently on let's say zero right  which one we want to go to four right previous   one it's going to go to four previous gonna go  to three two one and then the last one is going   to get a message saying we are on the first one  perfect now we need to do is add in the next the   next is relatively simple but we do need to make  a few adjustments so what i'm going to do is just   simply copy everything we have here and then  make the necessary adjustments so i'm going to   paste it in there okay so we're going to set that  adjustment id if the adjustment id is 0 what do i   want to do then i want to to also know is b5 is  1 that means there's no saved adjustments please   save any adjustments before navigating right can't  navigate to next if there's no saved ones however   if it is b3 equals b3 plus one right we're going  to go to the next one whatever's in the current   one b3 equals b3 plus one right the next one okay  but what if let's go ahead if the adjustment id is   equal to the last one the last one let's copy  this and change it to b5 a little bit quicker   so that means what if what if they're going  um forward but they're on the last one   b5 what is the last one the last one is b5  minus one that would be the last one right b5   minus one b5 remember b5 is the maximum right  so that means we know our last one's four   right so what if they're already on four what  if they go they go all the way on they can't go   beyond four because we haven't created a number  five yet so we need to let the user know so if b5   minus if the adjustment is equal to b5 minus one  you're already at the last right change that to   last the last adjustment last last adjustment  created okay and exit this up so we know that   okay otherwise b3 equals b3 plus one set the next  and we'll change this to next not the previous   that's it next adjustment id okay and  then run the macro inventory load okay   saving our work here and then what we'll do is  now we can assign that macro to the next button   and then we'll click assign macro and  we'll just send this to the next okay   very good next adjustment okay so adding them here  so now we're navigating here to two to three to   four and we get to the last one and it's gonna  let us know we're at the last and perfect all   we do the last macro print the worksheet that's it  print that inventory worksheet we're going to base   it on a dynamic row i want to include starting at  d5 and all the way to the last row so we're going   to set that print range dynamically within the  code so that's the first thing that we're going   to do inside there all right so the print invoice  so all we need to do is set that print range and   then we'll be up to date on that pretty easy so  first of all i want to get the last row what is   that last row the last row is equal to you can use  a 99 we're going to get the last row of course we   need to set the worksheet right so we can do with  invoice worksheet okay and then we'll just make   sure that that last row fits in there although we  probably don't necessarily need the width in this   case we could easily add the sheet okay so once  we've added the sheet if i want to make sure that   that it contains data so if the last row is  less than 7 then let the user know message   box there's no data please make sure to  add items before printing the worksheet   okay great and then exit the sub right there's  no data they can't continue on assuming that   there is data all we want to do is we want to set  the page print area so the page setup dot print   area is going to be equal to what is it what are  we starting out well i basically want to go from   d5 all the way to all the way to let's say the  actual quantity so they can write that in all the   way through column l so let's write that in so d  through l in the last row so it's going to be able   to use the absolute d and then here all the way  through string seven through seven seven through   and then we go l right we don't know the last l  dollar sign and the last row which is dynamic okay   so that's it that's the set of the print area the  rest of this just to print out so we're going to   print it out let's let's raise that up a little  bit so you can see it print out okay so what are   we going to print out from we don't necessarily  want to set any pages so we can just tab over that   combat 2. we don't necessarily make copies do  we want to preview it i would say no we don't   want to preview that right we want to let's  set it to the active printer yes so that's   true we want to set that whatever the active  printer is then print a file no false collate no   print to file name no just no fill file name and  ignore print areas false we want to make sure we   maintain the print areas okay so that looks pretty  good that's pretty much it so now what i'm going   to do is just copy this right and then we're going  to assign it to that button that we created okay   so print the worksheet here right click assign the  macro paste in that and click ok and again always   saving our work before we run the macros and then  print that worksheet it's going to print to that   default printer right here it is and taking a look  at that let's get my snagit as my default printer   so let's minimize that and we can see what that  looks like okay i like that that's looking pretty   good we'll bring that down here so we can see  what it looks like that is the page so let's set   up that i think we need to make sure that we've  set up i want that header row to show up so let's   update that that header row very very important  right we want that header row i want to make sure   that we're including d5 d5 is the correct row so  let's update that range right there d7 should be   d5 here there we go running that macro once again  because i want to make sure that that header row   comes in there print that worksheet making  sure that we put that header row okay that   looks nice there we go so we've got that there  and we'll reduce that so you can see it all here   very good so we've printed it out printed out and  nice so we've got all our updates we've got our   actual quantity here if we want but probably going  to do a new one so let's do new adjustment here   getting all that data in here printing that  worksheet so we can get we're going to have   two pages in this case and then we got a print all  right let's take a look that looks really nice so   we've they can now write in their actual quantity  we've got all the barcodes that can be scanned in   very very cool this has been a really fantastic  training from scratch i hope you like these some   are going to be from scratch if they're relatively  easy like this one some will be of course   going through the code step by step i appreciate  all of your patience thank you for joining me   these long trainings and i can't wait to see you  next week don't forget if you want to support us   i've got 200 of my best templates now available  for you can click the link down below it's just 77   a great deal thank you so much  and we'll see you next week you

Channel: Excel For Freelancers

Views: 148,338

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Physical Inventory Worksheet, Inventory worksheet, Inventory Tracking, Inventory Control Sheet, Inventory Excel, Excel Inventory Sheet, Excel Inventory Worksheet, Excel Inventory Tracking, Track Inventory Worksheet, Track Inventory In excel, Count Inventory In Excel, Excel Inventory

Id: 4hZUTWnMWaE

Length: 111min 49sec (6709 seconds)

Published: Tue Feb 01 2022