Discover the Best Sample Bill Format in Excel for Hospitality

Sample bill format in excel for Hospitality

hello this is randy with excel for freelancers  and welcome to the hotel booking application   in this week's training i'm going to  teach you how to create an amazing   drag and drop gantt chart where we're going to  use it with hotel but you can use it for any type   of application it's going to be an incredible  unique training i can't wait let's get started alright thanks so much for joining us today i've  got a really fantastic training we're going to be   using the incredible drag and dop functionality  that maybe you have seen in other trainings we're   going to go over it in a really amazing gantt  chart we're going to create you can use this for   projects renting out spaces you can use this for  almost any type of application we're going to be   creating a hotel application but you can use this  for almost any type of industry so we're going to   show you just how to do that i'm going to walk you  step by step through all the code and every part   i've designed just a bit of this on the screen  that's going to help move this training faster but   don't worry i'm going to go over every part of it  a lot of the applications we do the designs from   scratch but they tend to be much longer trainings  and i want to get to really the focus i want to go   through the scheduling i want to go through the  application the creating the units the hotel the   bookings and all the information that's going  to help you create those applications for the   design purposes i've already done that for a lot  of applications so i just want to let you know if   you do like these trainings and i hope you do i do  my best i work really hard to bring you something   unique each and every week the best thing you can  do is just three things like the video below make   sure you share this application of course comment  below i create these every week for you and i   always respond to each and every comment so if you  have any questions concerned or just want to say   hello comment down below i'll make sure to respond  within a day or two as i make sure to make that a   part of every my day you're important to me you're  following your subscriptions really means a lot so   i appreciate that i want to make sure that you get  this application to do that all you need to do is   click the links down in the description i always  make these applications free for you you can use   your facebook or email to get that delivered right  to you within just a minute or so if you want over   150 applications i've got a whole zip file you  don't need to download them and it includes an   application library so that means you can click  single click to open the application single click   to view the associated training videos i put all  that into compiled it for just 37 cents a workbook   that means 56 dollars for us dollars to get this  150 my best applications so if you like this   and you want to support our training each and  every week you can go ahead and pick that up   i'll include the links down below for that as well  all right let's get started we've got a lot to   cover on this i'm going to try to let's see if we  can get this within an hour it might go a little   over this time let's see how we do helping taking  care of the design elements is going to save us a   lot of time on this so let's go over what i have  and then we're going to go over what we want to do   and then we're going to get right into it and  show you exactly what we're going to do so   the focus of this is to basically allow a user to  create additional bookings for a hotel so we'll   have a list of rooms here and then the associated  dates will have a month obviously month and year   here using previous and next buttons can allow us  to navigate previous or next months users will be   able to create a new book and clicking on that  that's going to clear all of these fields i set   up these fields before and i've got also got a  pop-up calendar that's already associated with   this pop-up calendar we've gone over before but  that's okay if you download this application you   can get it for free of course in this application  you can use that in any application as well okay   so the idea is really simple the form basically  now all the bookings are saved in a database here   and a booking database is pretty relatively simple  i've kept it really simple i've got a booking id   a booking date the guest name the room room type  status check in check out number of guests and   special questions something pretty basic but  it should be sufficient and the only other   thing is we have a list of rooms now the  list of rooms are also we have a room type   and the idea we want to room type is maybe we  only want to show in our list here certain room   types maybe a guest is looking for a deluxe or  city or penthouse or something we can filter it   that way we only see the availability based on  the room type so i thought that would be helpful   so if you don't you can see all rooms or just a  filter so let's take a look so that's all we have   is room now we also have some named ranges that  i've set up before so basically all we're going to   do as far as the room type is we're going to set  up an advanced filter here and then the results of   all of that here so we're going to go through  that let's take a look at some named ranges that   i've created already so we can go through those  there's only a few of them so not too much we're   going to go to the name manager and now we have  booking checking so we have some bookings based   on the bookings here booking check-in is a list of  all the check-ins based on all the check-ins and   that's going to be a dynamic named range and that  means as this list grows so does that named range   and that's going to be based on just the check-ins  pretty much the same thing for the check out we   have all the checkout dates in a named range  called booking checkout and the reason we want   these in a name range is because we have to check  for conflicts so later on i'm going to show you a   formula a sum if formula it's going to allow us  to figure out if there's conflicts so if there's   conflicts we're going to make sure to pay special  attention to those so the user knows that there's   a conflict schedule booking id is just that it's  a list of dynamic booking ids based on that and   then of course the rooms also so i have four  named ranges for a booking database booking id   room check in check out most of that's going  to help us the room the check-in checkouts can   help us look for conflicts so that's the reason  what else do we have we have uh some named ranges   associated with the pop-up we're not going to go  over this the ex criteria in the ra the criteria   and the extract are based on uh created by vba  when we create the advanced filters so if you see   these in your workbook you don't know where they  came from every time an advanced filter is created   one of these are created on a per sheet basis  notice we have one for rooms and one for bookings   criteria for each and extract for each so that's  where that came from we'll go over that a little   bit now the room numbers i've also got a dynamic  list of room numbers and that's based on the room   database here so just three databases excuse me  just two databases rooms and bookings so that's   it rooms is and we have room types also so  rooms and room types pretty simple stuff   nothing too complex as far as the database is  concerned and i have a drop down list we want to   drop down let's have room types with all and what  does this mean i want a list of all the room types   and i want to include the words all right why do  we want that we want that as a data validation   i'm going to place that data validation right in  here so that we know we can select between those   and i also have a status let's take a look at this  the status i want to know the status pending book   to check in checked out so we can assign a status  to every single booking and why is that important   it's important to know and then we can also color  code those statuses and that means the shapes   those drag and drop shapes that we're going to  use for scheduling can have different colors based   on the booking status and they're dynamic what  does that mean that means as a user or you as a   developer change these colors so will the colors  on the sheets very very simple so instead of   programming it and this is really great because  theoretically if you had a pop-up color picker   which they're pretty common in vba you can add  your end users to create their own colors so   that means that your end users would select a  specific color and then that's color would be   in the cell and then the shapes will take those  cells and i'm going to show you that within vba   we've gone over that in a task scheduler this is  going to be really amazing because this is dynamic   drag and drop gantt chart so basically this type  of training you can use this for projects you   could use this for tasks you can use this for not  only room booking but let's say you're renting out   equipment or let's say you're have a meeting room  that you want to rent out so basically any kind of   asset that you want to rent out this is perfect  for on a daily basis on an hourly basis you   probably want to make some more changes but on a  daily basis this is perfect this can be customized   for any type of asset rental a hotel room is an  asset that you're renting out based on a per night   basis so whether it's a meeting room or anything  or even a event hall or something like that   scheduling is great and the ability to  change dates or change length of dates   is super simple we even use drag and drop so you  can extend to stay simply by dragging the shape   very cool stuff very advanced excel so we're  going to get into that and i'm going to walk   you step by step so don't worry about that so  we have colors associated with that and then we   have a start date that start date is going to be  based on right here based on the august that's the   start date of that's going to be dynamic based on  the month okay so that's pretty much it for our   named ranges in the manager so we're going to go  through step by step so what do we want to happen   well basically here's what i want to happen i want  to do a lot of things when i select on a shape and   the shapes are going to be extended across here  as long as the booking i want that information   to load the booking when i create a new booking i  want all the fields to be cleared out and then i   want the user to enter the room number which is of  course a dynamic named range already said based on   our rooms number room numbers here as we went over  before check-in is the pop-up calendar check-outs   will be a pop-up calendar so we can enter a date  number of guess is just a field not too important   on this one just basic status is based off course  on our data validation that we just went over   located on statuses which is right here and then  we have some special instructions or special notes   that's going to get saved in the database the  save booking this booking here is only going to   be visible when when on a new booking everything  else so this group here this group of buttons here   that i've set up these are just shapes and icons  nothing else there's no macros assigned to it   we've created these in the past and all just  if you want to create your own buttons all you   need to do is just insert shape here all i did  was use in this case i'm using a square shape   and then i've set the format here to just this  one that's all i did i added an icon and group   pretty simple stuff so nothing too complex so i  don't want to take too much time going over that   so the idea is this group here this group  of buttons has been called existing group   and it's used for existing bookings bookings  that have already been saved this button here   this button is associated with a new booking  group this button will only be displayed for   new groups the reason we have two groups so that  we can actually place them in the same location   just like that because only one of those is going  to show up at the same time either the existing or   the new so we can place those over because only  one will show up so we can do that i just wanted   to be able to show you both no macros have been  assigned to them yet so we want to make sure bring   it up a little bit so we can actually assign a  macro to that and then we'll place it in the lower   so the idea is basically once we have all  the information we do have some required   fields like guest name select room check in  those are required fields and probably status   all those are required once we assign those i  want to take that brand new one and put it right   in here in a brand new row into the bookings  if it's an existing if it's already existing   we want to then update whatever the existing is  and then once it's updated i want to update the   schedule here so every time we save or update  a hotel booking we want to make sure that that   schedule gets updated all right so we've got  a little bit different information very very   simple here not a whole lot of things going on  i want to differentiate between a new booking   and an existing booking when we click on a booking  here it's going to load that information up here   that would be an existing booking is so we want  to differentiate we can use true or false to know   whether it's also i want to know what booking  id we selected if we select a new booking   booking id is going to clear out because there  is no booking but if we select any kind of a   booking here when it's here i want that booking  id i want to know was the booking ideas one two   three or four we only have four right now i want  to know which one because if i'm going to load all   of this information i need to know what the id is  not only do i need to know the idea i need to know   that booking number four is on row number seven  so how do we do that we use just a match for that   and it is located right here i'll go  over that in a second let's go in order   selected shape when i select a specific booking  i want the name of that shape to show up right   here that's so we know we can work with that  shape once it's displayed there we can work   with it okay again i want to know the booking row  what rows it if row number booking number four is   located on row seven we can use match for that  we wrap that on iferror always we don't want any   errors to show up in these cells because once vba  takes a look at the cell if there's an error vba   will return a bug so we want to make sure that  we always wrap that on if air so what are we   going to do we're going to use match and we're  going to match based on the booking id booking   id is located in b4 and we're going to match it  within the named range called booking id that's   the named range i just showed you and we want an  exact match so we're going to use zero we're going   to add three why are we adding three i'm adding  three because the first booking starts in row   four right here so if if i get one it's going  to return if i just use match and i put booking   number one in it's gonna return one because it's  the first match but i don't want one i want four   i want the row number so all we need to do is  add three to that so then we're set up okay so   assuming that we've added three if there's  an error we're just gonna blank spaces so   that's going to get us our row number that's very  important because we need to load all the details   in here and we need to pull all the details  include the booking date and everything else   from that so how do we pull it we know the row  number but how do we get all the information   in we're going to use mapping for that you've seen  this before and we're going to map just a certain   number of cells if you've followed my videos but  i'll go over with you and basically this cell j2   is mapped to guest name and i mean map what do i  mean by that j2 is right here j2 guest name so all   i need to do is determine the row then let's say  we found row 5 booking id 2. terry tester where am   i going to put terry tester i want to put that in  j2 right here so what about the next one if i want   the room 105 where am i going to put room 105 i  want to put it in ae2 and this helps us out a lot   in coding because all i need to do is loop from  2 to 10 or whatever or three to ten in this case   and i just need to pull every time i get the  row i've got the row i go through the row i find   what's here and i place it in here i find what's  here and i place it here so that's data mapping   that saves us a ton of time we can do the same  thing we use the same data mapping whether we're   loading information from the database into our  form or we are saving information from our form   into the database whether it's a new or existing  the reason we don't have mapping here is because   we manually put these in i only want to put these  in manually these are fine because booking date's   not going to get loaded and i just want to know  what data was booked that's all i want to do   there's no place for a booking date here i just  i didn't put that in i guess you could put it   up here some other place but i didn't put it in  i just wanted to save in the database it seemed   unnecessary but i wanted to put it in just in case  if you guys used it and so basically what we're   going to do is i'm just going to bring all this  information in here based on whatever information   and bring it back into the form so it works both  ways sending saving the form data into the data   into the database and bringing the database form  so that's what we're going to do we're going to   start writing code in just a moment i wanted to  go over everything that we need to do so basically   what do i want to do i want to have the rooms the  bookings show up in a thing but the first way we   need to do is we need to get this information into  the database that is really important so let's   do that let's write some code to get in some  information inside the database so i'll let's   call this save i'm going to save it i save or  update we're going to use the same macro to both   save and update and what does that mean that  means this update button is going to be tied   or this saved booking both those are going to  be tied to the same macros called save update   let's put in some information first so we can see  that lisa leslie and we'll put in some information   let's say we already have rooms let's say we're on  room 106 and we're going to give her a check in of   keeping the month august 3rd all the way check  out to let's just say august 6. and we'll put   number of guests to and then their status we can  assign any status i haven't automated these status   but it would be nice to automate them and then we  can just put in special requests so let's say um   big bed okay so now we have that so basically what  i want to do is now i want to write a macro it's   a relatively simple map to take all this data  assuming it's a new hotel booking and place it   inside this table so how do we do that well let's  go into vba and write a small macro that's going   to allow us to do that how do we do that well into  the developers tab we can go into visual basic to   get there if you don't have this alt f11 will get  you there but you can also get that developers tab   if it's not visible for you into the customized  ribbon just make sure developers checked right   here okay so once we have it there you can go back  into the developers and i've got just some basic   framework about i didn't write any macros for you  because i want to make sure to write those along   with you so you can watch me as i create this and  as i bring life into this application right now   it's just a design phase but we're going to bring  it to life and give it functionality and features   every step with you so i've got pop-up calendar  that's the only information that i've written   this is a large large macro all you need to do is  pretty much drag and drop this module into your   application we're not going to go over that i do  have specific trainings on some of the features on   this just type in pop calendar date picker on my  youtube channel you can find more information that   this training is not focused on that pop-up  calendar but just one these macros allow us   to create this really beautiful shape-based pop-up  calendar which you may like i created that a few   years ago myself all right so the scheduling max  we have scheduling max we're going to get to that   second but basically what we want to focus on  right now is the booking macros so i want to write   just four macros we're going to write one  macro to load the information one to save   it or update it one to new and one to delete  let's focus on the save and update and then   we'll get to the booking load so let's bring this  down here because i want to get to that next and   then we'll focus on the save and update now i've  dimensioned a few variables the row booking row   as long and the booking column is long and the  selected booking is the shape so those are the   variables that we're going to use within these  macros it's not so many again just a few of them   so the first thing i want to do is focus on  save and update the first thing we have to do is   we have to make sure that we have some required  fields that those get required we want to make   sure the users entered at least the basic ones  concerning like j2 which is our guest name a   room number that's required and we need check  in and check out dates without that we can't   save a booking so we want to make sure that we  least have that the minimal information so we're   going to write some and just call this check for  required fields so that's the first thing we want   to do and what fields are we have yeah we'll go  we're going to focus on which sheet one so that's   the first thing first thing we want to do sheet1  we want to make sure we're setting that so if dot   range j2 that's the name we want to make sure j2  has a value j2 value equals empty or there's more   what other fields let's just copy and paste this  it's a little bit quicker or what we also have   af2 what is af2 that is the room nut select  room we need that so a f2 we gotta can't have   that empty we also have what is this one this is  called ax2 we need that that is our check-in date   so let's do ax2 x2 and then at least one more we  do need at least two more bi 2 this one here bi2   we need to check out date so we have to make  sure that that is required so let's put in   b i2 that can't be empty and also i do want  to make sure that um that there is a status   because the status are based on color so i  don't want that to be blank as well so let's   make sure r4 is or let's put in that once more and  write in then so that's going to automate so then   we'll change that we're going to update that to  r4 instead of j2 okay so now we've checked for all   so what happens if if any one of those are  empty we need to let the user know to make sure   that they have actually entered wrong so let's  do that message box let the user know please   please fill in all required fields before before  saving this booking okay so we just let them know   and then we're going to exit the sub nothing we  can do unless those fields have been required   now assuming that they have been filled and we  can continue on the first thing what i want to do   is i need to make sure again we need to run  another check i can't have a checkout date   that's on or before the check-in date we need  to make sure it's actually after so let's do   that right now if dot range b i 2 right  bi 2 that is our checkout date dot value is less than or equal to is less than or equal to  dot range ax that of course our check in date a x   2 if it's less than or equal to there's a  problem then we need to let these know then   what do we want to do message box please make  sure the check out date is after after the check   in date okay that's important just let the  user know and exit the sub can't have we can't   have a check in date that's on or we can't have a  checkout date that's on or after the check-in date   okay so now that we've covered and then let's let  maybe we'll select one cell so we can do dot range bi2 dot select so what we're going to do is we're  going to select that for them so that they can   put in quickly the pop calendar will  show up automatically as soon as they   select it and i'll walk you through the code on  that okay so then we're going to exit nothing   we could do now what i want to do is i want to  determine is it a new booking or not right so   how do we know if it's a new booking this b2  is going to let us know if it's a new booking   because as soon as we click the word as soon  as we click the button new book inc when we   write that macro we're going to change this to  true so b2 we need to know is b2 true or not   so if dot range b2 dot value equals true then  we know it's a new booking what else can we   do there's something else that's even better  than that if booking id is empty then we know   the booking row remember there's an error so  why don't we use b8 if b8 is empty that's even   better so b is empty then we know it's a  new booking so we're going to do that b8 equals empty then we know it's in then then new  booking okay and if and then we'll write an else   in here okay so what if it is a new booking if  it's a new booking we need to assign a specific   row what row are we going to do booking row  remember we've already dimensioned the booking   row up here as long so the booking row is going  to be equal to that's the auto hotkey that helps   me automate that text in case you saw me type it  in it's going to be sheet 2 a and then that's the   that's the last row of the value but i need the  first available row so we're going to do plus run   that's the booking row that's the first available  row in sheet 2. that's where bookings are stored   so once we know the booking row what i want to  do is i want to take the next booking id how do   we know what the next booking id and that's going  to be right here we're going to use a max formula   what i'm going to do is i'm going to take all the  booking ids make sure if you're going to use this   we need to make absolutely sure that these numeric  okay they can't be alphanumeric they can't contain   any letters they must be numeric the next idea  the max of these plus one is going to be the five   so that's exactly where you we're using the max  functionality and if there's an error if there's   no data there would be an error right there's  max of nothing so if there's an error in this   case i want to put one that would be the first  booking id just in case there's no data at all   so that's going to set up right in this case  our next booking id is 5. so we can take that   i want to put that in two places i want to put  that right here under booking id and i want to   place that right here in our first available  row and i want to put it in column a so we're   going to do that right now within vba i'm going to  place in both of those locations so dot range b4   value equals dot range b9 that's going to set  the booking id b9 value set booking id that's   the first place i want but what's the second  place dot range a in fact this should be sheet 2   dot range a and booking row which we just  created equals in this case of course   b9 value so we can just put b9 we can also  copy this pic copy and paste this right here   we're going to place it right in here so that's  it so we're going to place it in both of those   locations once on sheet two and one on sheet one  so that's if it's a new so we've done that so what   else do we need to do i want to set up the booking  date i want to know when it was originally booked   and so we're going to place that in b in this  case but only for new bookings so in this case   we're going to copy and paste this i'm going to  update the a to a b then i'll put in now equals   now and that's going to put in the current  date and time so just put in current current   i'm just going to put in current date and time   okay so that's it that's pretty much that's all  i want to do for a new booking what if it is an   existing booking then we already know the row  so we got to set the row if it's an existing   booking we know the row is going to be located  right here into bh so we can set that right now   booking row equals dot range b8 in this case  b8 that's the booking row existing booking row   we need that to save the data because everything  else we're going to add automatically so now that   we've set the booking route now what we can do is  take use our mapping and run a loop in this case   it's going to be 3 to 10. column 3 all the way to  column 10 how do we know this is column 10 equals   column 10. we know that's column 10 so i want to  take that and i want to take all the information   from whatever we have added here and i want to  put it all into here including the standard view   this what is this let's take a look at that  before we go over that is a formula i need to know   based on room 106 what i want to do is i want to  look up room 106 and i want to determine what the   room type is i want to take that room type i want  to display it right here using this formula and i   want to take that room type and i want to add it  into the bookings right into here now keep in mind   that this this particular field only because it's  based on a formula we are going to bring it one   direction and what i mean by that from the form  into the table but not but not from the table   into the form why is that because it's based on  a formula i don't want to replace this formula   because that way if the room changes so does the  view so does the information the details about   that the room type so how do we get that what  we're going to do is we're going to index room   types that's the named range and what is the row  we're going to use based on the room numbers so   we're going to be indexing all those room types  we're going to be basing it on the room number   whatever room number has been selected in ae2  and we're going to use just column number one   if there's an error we're going to keep it  blank so that's how we determine the actual   room type and that's going to be helpful  when we want to show only specific room type   this is a dynamic drop down list based  on this list right here all room types   it is simply based on this information right here  including all room types this is a drop down list   basically here so that's it so that's all we have  there okay so let's get back into our code and   show where we were so now we have the row number  now we can set our columns so we're going to   create we've already created a name branch called  booking column here so let's write in that so   4 booking column equals 3 to 10. we know  we have to loop make sure we close our loop   next booking column okay so once we have our loop  it's really quite simple what do we want to do   i want to take the information from our form  and place it into our table so how do we do that   well we can do that just very simply sheet  to that's our booking dot cells we're using   cells because both the column and the row  are dynamic what is our row the rows booking   row what is our column booking column dot value  so what is that going to equal equals based on our   dot range wise we've already said dot sheet one so  where we don't need we're already specifying sheet   one so we just need to use dot range now what  is the range where are we gonna find that range   i need to find that range the range is located  right here in row one and in the variable column   three four five six so we know the range so all  i need to do is extract that range from row one   right here in sheet two so how do we do that we  do that just like this range equals sheet to dot   cells using cells again rows1 column is booking  column dot value so this right here this value   is the range so that range so dot value here again  is going to place that value place whatever is   in this range and place it inside this row as we  move through all the columns so that's it that's   all we need to do and then all we need to do again  is update the shape so now that we have all of the   data from here we know it's not we've we got to  make sure that our new booking group is hidden and   that our existing booking group is displayed those  two groups we need to update to make sure that the   user knows that now they can update it but it's  no longer a new booking so dot shapes new booking   group dot visible equals mso false it's no longer  a new one so we need to put in false what about   the other one the other one we need to make sure  it's visible dot shapes existing booking group   dot visible equals and so true we want  to show that show existing button group   and then hide the new button group hide new  button group okay so we've got those that's very   important and again we also want to make sure it's  no longer a new so we want to certainly make sure   that b2 is set to false so we can do that here  dot range b2 dot value equals false okay so   that's it let's set up set new booking to false  no longer new booking so we could set that up okay   that's it that's all we need to do and then what  we want to do is we want to actually run the macro   that we have not created yet called scheduler  fest so after after i created i want to update   the schedule right after i add i want to make sure  that that booking assuming that it's in this month   appears on this schedule so we need to make sure  we run that macro we're going to create a macro   called schedule refresh and so let's just write  that in here so we don't have to come back and do   it schedule refresh now i've already written just  the type of the macro here i've just written the   header so that's going to automatically update and  it's going to be right here but there's nothing in   that macro i'm going to write it with you there's  nothing here yet so just keep that in mind that's   what we want to do okay that's it that's all we  need to do let's of course save our work and let's   assign that macro i want to assign that macro to  1 save booking so i'm going to right click assign   the macro booking save or update and i'm going  to do the same thing for our update button so   our update is two parts it's the icon and the  button so holding down the control right clicking   and click assign macro we're gonna  do the same exact thing booking   save or update okay so it doesn't matter what  button we click and that actually let's just move   this down we want to make sure it's in the right  place before it gets hidden and so all we need to   do in this case is just click that button please  fill in all the required fields and that means   one of the fields we have we'd better double check  that one of the fields that we have is not correct   this should be a e2 not f 8e2 okay so then we're  good let's go ahead and update that there we   go let's take a look in the bookings now it's  all the information the date the name the room   this looks good eight three to eight six two guess  and bigger okay it's all been saved table perfect   so now we have it let's write additional macros i  want to write a macro that says new so as soon as   we click this button all the information gets  cleared out and the brand new one appears so   let's do that right now that's under new booking  so we're going to scroll down here and here's   my new booking and let's take a look at that and  just write in a few macros it's not going to take   very long in fact i've written it before because  there's a lot of fields to clear out so i just   wanted to write one of the lines so first thing we  want to do is set the new book into true so with   sheet one we're always focused on she wanted  this dot range b2 b2 is our one that lets us know   whether it's a new or not equals true set set new  booking to true and i want to clear out a bunch of   fields a lot of fields so that let me go ahead  and copy this it's going to be i've got it here   just to save us some time so you don't have to sit  and watch me type in all these fields so basically   here is it here's all the fields that we need  to clear out it's a lot and i usually get it   wrong so i wanted to make sure i get it right so  basically what's going to now keep in mind that   when you clear notice that these are merged fields  so when we clear this out we need to make sure   j 2 through x is cleared so j 2. you want to  clear out not just the cell and individual   cell but make sure each individual j2 through x2  a e2 through ai ax to b size so i've cleared out   all the fields within that that's all  we need to do and then again we want to   update the shape sets in this case what we're  going to do is we're going to copy these   shapes then we're going to just change over we're  going to alternate the true the false so this one   is going to be true in this case we want to show  the new button group and in this case we want to   hide the existing because it's a new one so hide  the existing and this one's going to become false   okay great so that's pretty much it a  very very simple very very easy macro   and all we need to do is assign this to the  button so we're going to click on the button   holding down the control clicking on the  individual shape and the individual icon   right click assign macro and then just  make sure it says booking new click ok   now click the button and that's perfect  that's exactly all the fields got cleared out   this is for filtering the schedule so this is not  part of it and the word saved booking is perfect   that's it okay what about when i want to load  a booking now let's write in that when i load a   booking i want to make sure when i click something  it loads but there's nothing to click we haven't   created a schedule so what we're going to do is  we're going to update this schedule we're going   to create the buttons on the all of the shapes  on this schedule before we do the load because   that we can't click and there's nothing to load  because there are no bookings here displayed yet   so let's get those bookings in let's create that  first and then we're going to show you exactly   how we do that now let's update a little bit here  we have a shape here now basically this shape is   called sample shape all it is is simply a square  shape that i've created it is this shape that's   going to be the foundation for all of our shapes  in our bookings every time we refresh the schedule   i want to delete all the shapes within this within  this region here and i want to create brand new   ones and it's going to be based on this so what  i'm going to do is i'm going to take this shape   called sample shape and i'm going to duplicate  it once i duplicate it within vba i then want to   position it color it and show it up and assign a  macro to it and this macro is going to be shine   the macro's already been assigned to make it a  little bit easier what macro is that there is no   there is no code inside the macro but i wanted  to show you now there's a macro that we're going   to use called select shapes when we go into the  schedule and we look down here we have something   called select booking that is the macro that i  want to sign because when i select that what do i   want to happen let's duplicate it again and bring  it up here so i can show you once i duplicate i'm   going to have a booking and you'll have all  the information here you'll put their name in   when i select it i want that information to load  up inside here i want it all to load so how do we   do that we're going to show you the first thing  we need to do is get these shapes displayed   with just a little bit of a macro to do that we're  going to call that schedule refresh and it's going   to put take all of this information all these  bookings here and place them right on our schedule   here based on shapes it's going to be really  really great that way the reason we're using shape   is because when we have a booking we can drag  and drop that shape anywhere we can change rooms   we can change dates we can change durations really  amazing and it's not that much code to do it so i   can't wait to share that with you so let's get  into it let's focus on the schedule refresh   because that's the one that's going to build out  all the shapes so i'm going to take you step by   step through that so you can see exactly how we  are going to do that so the first thing we want   to do is to make sure we're going to focus on  with sheet 1 as we're dealing mostly with g1 with   sheet one now we've defined some variables let's  go over just some of these briefly the last   booking row we need to know the last row that's  the last row here we're going to run it through   an advanced filter now i need to know the advanced  filter it's going to be based on what it's going   to be based on this date august right august  as we click previous this is going to change   for the previous month or next to the next month  but basically it's all going to be based on this   called the start date this is the named range  i created based on this and this is basically   august 1st but it's been formatted for  just the month and just the year but it's   actually august 1st how do i know that well  if i just take another cell let's just take   let's just take this one equals and then i change  this to august 1st you're going to see that it's   automatically august 1st you see how that is based  so it's just a formatting it's been and how do i   get a formatting that look like that it's a full  month it's a custom format if i go into more   numbers we're going to see that it's a full month  which means 4 m's and then the space and then the   four y's which means they're all four digits so  it's a custom format but it's actually august   first and so that's important because that's going  to help us so what i want to do now is basically   i want to take all this data here and i want to  determine if there's any check out date that is   less than or equal to inside the month or  check in date that's less than or equal to   then i need to know that so basically any  booking inside the month even if it's a checking   out even if we're checking in before the end  of the month but checking out inside the month   or likewise we're checking in in the month and  checking out the next month even that i need   to know so how do we do that well that's an or  condition or condition and basically when we have   dates we're going to create a criteria in advanced  filters so i'm going to take all of this data and   i'm going to run it through this filter here and  basically based on dates but these are numbers   right how are these dates this is the what i want  start date notice it says start date that's the   date that we've defined automatically so if it's  great if the check in in this case is greater than   or equal to the start date and these are and and  or so or is here or is the second line and an and   so if there's a check-in date that's greater than  the start date and a check-in date that's less   than the end of the month how do i get the end of  the month all we're going to do is we're going to   take eo month based on the start date and no  months i've had in no months before that means   the current month 0 means the current month so the  end of that month in that case it's of course it's   august 31st so any checking date within that month  that's fine i want that or any check out date   within the month any check out date notice we're  focused on checkout or checkout date check in so   remember this is and right here and this is or or  and keep that in mind or check out and to check   out here so what is this a checkout date that's  greater than or equal to start date or a check out   date that is less than that means basically all  i'm saying is a checking a checkout date within   the month or a check-in day within a month that's  a simple way of saying it this criteria is going   to handle that then what i want to do is set the  criteria advanced filter that criteria in fact if   there's a filter on this i need to know what if we  want to show only certain room types not only do i   want to show but in this case we're going to show  all rooms but what if i only want to show deluxity   for our standard view if i only want to show one  of those in that case i need to have additional   criteria so how do we do that we can do that  with a formula because it's not going to change   so let's take a look at that if or the schedule  ai6 that is that is the cell that we're focused   on the schedule equals all rooms or it's empty  in either one of those cases if it's empty   then what i want to do is just double close  i want to show empty under the room types   any other condition i want to show the actual room  type so that way when a user changes might give me   a bug so when a user changes this i want to show  that room but i want to show it both right because   it's both this and this anytime we have multiple  criteria in an or scenario both of them must   take on this room type so notice i have the same  formula in both cells that way they both respond   this way only when i run our advanced filter  from this data from n 2 through r 4 all that   information is going to be person and the results  are going to come here then what i'm going to do   is i'm going to take all this data i'm going to  convert it into shapes and i'm going to bring it   right back into the scheduling based on shapes  okay so you understand what's going on let's   bring this back to all so that's going to show no  room so we can show all the data all rooms here   and there we go so now it's blank again so that's  what we're going to do inside the macro so i want   to run that so that is exactly why we have the  last booking row we run the advanced filter i   want to know the results row so we need the last  results row in this case the last results row is   six but of course we need to loop through starting  at three going all the way to the last results row   looping through those we also need to know the  result row as we loop through each row we need   to keep track three four five six so i need  to keep track that's why i've got a variable   called results row i need to know the schedule  row that we're going to be placing it on this is   the schedule row starting in row nine where is it  going to be placed i also need to know the column   notice that with day one these are days i should  have told you that earlier these are days one   i guess it's kind of obvious as we go through  the months so each day has two columns why is   that because theoretically a check out will appear  here a check in will appear here because it takes   half a day so if you have a guest checking out on  two and another guest checking in in the same room   we can have the same room on the same day but so  the check out contains the second half of the day   the check in contains the first half of the day  okay so that's why we have that and that these   are also based on dates notice it's based on the  start date again and this is just the start date   plus one but what about when we get to february we  get to february we want to show 8 28 but maybe not   29 maybe not 30 maybe not 31 so those are special  instances so i gotta write additional formula to   display that i don't want to display 29 you know  if it unless it's a leap year or less in february   or unless it's a month i don't want to display 31  if the month only has 30 days so how do we check   for that well the best way to do that is just to  determine if we could say if the month of this is   the same as the month of this plus one in other  words 28 plus one is it the same month or not   if we do this 31 plus 30 is it the same month or  not so we can do that if the month of bka plus   1 is the same month if they're in the same month  number this is going to determine the month number   then what we want to do is just simply add one but  if it's not in the same month then keep it blank   then put it blank because there's no date that's  all we have to do so i've written the same formula   here for all the three the last days so that we  can then for the months that don't have those   end dates it can just show blank spot so that's  how we got that and let's continue back into the   variables so we need to know the schedule column  and the duration columns how many columns is it   based on the booking so we need all that i need  to know the booking row the row that we're booking   all of course in the booking call and that's  important and the last room row and i want to know   the last room why do we want to know the last room  also i want to know a list of rooms right i've got   to know what the room type is i only either  i'm going to show all the rooms here or i'm   going to show just rooms based on a specific only  deluxe city or only standard view rooms so i need   to run an advanced filter based on rooms because  i only want to show those certain rooms that are   contained within this filter so again it's inside  our rooms database it's that exact same formula   here exact same one so the same formula so as  soon as i change to deluxe city view in rooms   we see the deluxe city view this way we're going  to run our advanced filter for the room number   and it's going to be based on this room type here  here's our criteria this way in vba only those   rooms that meet this specific criteria are going  to show up here once we have the list of rooms i'm   going to take all these rooms and bring it right  back into the schedule here showing those rooms   then all i need to do is match up the rooms with  the bookings and then create the schedule here   so let's do that let's continue inside  now that you understand that we also have   a few more room number booking that's detailed  strings for based on status and we'll get that   found room i need to determine if we find the room  we're going to we need to look for that room if   we find it on the list we know what row to place  that book ink and also a check in date and check   out data's date and the booking shape this shape  is important because we're going to define that   shape and we need to create that shape and then  we're going to set it as a shape okay so let's   get into let's write some macros now that we have  a understanding of the application let's go ahead   back into schedule refresh and continue with our  macro so the first thing we want to do is clear   b6 b6 is the selected shape row so just in case  there's any selected shape if i want to make sure   that this gets cleared out so let's do that right  now that's always going to keep track of whatever   shape we've selected but when we're refreshing  the schedule i want to clear out so dot range   b6 dot clear contents okay so that's going to  clear out clear the selected shape because when   we select a shape the shape name is going to go  into b6 so you'll see that shortly okay so then   what i want to do is i want to delete all of the  shapes on the sheet we're refreshing the schedule   basically any shapes here i want to delete  them automatically so how do we do that how do   we delay well if we set a specific name for those  shapes when we create those shapes if we assign a   specific name let's just call it let's say we say  booking and then the id so booking 1 or booking 2   or booking 3. if i give each shape a name and that  name contains the word booking then i know that to   delete it this name of course doesn't contain the  word booking none of our none of our other shapes   contain the word booking so we're pretty much safe  to delete any shape with the word booking on it so   we can do that with this one these lines of code  right here for each booking shape we've already   defined as a shape above in dot shapes that we've  already defined sheet one this morgan is writing   notes clear all booking shapes on sheet one those  are booking shapes not all shapes just booking   shapes next booking shape so what do we want to do  then if the first thing i want to do is i want to   just check to make sure that the type is an auto  shape type we want to make sure that so booking   shape dot type equals mso autoshape then okay so  let's close that end if now we're going to run   one more check if in string i'm checking for  the name in this case booking shape dot name   dot if it contains the word booking booking and  how do we know if it contains if this if the   in string is greater than zero then we know  it is then booking shape dot delete then we   can delete it because it contains the name  booking that's it that's all we need to do so that's going to delete all the shapes that  contain the booking in that contain the word   booking inside this sheet now what do we want to  do now we want to clear all the previous rooms any   rooms that are here maybe they've cleared so let's  clear d9 all the way through a high row let's just   clear that dot range d nine through d let's just  say nine nine nine that gives us a lot of rooms   dot clear contents and that's gonna clear the  rooms clear rooms from schedule now that we have   the rooms cleared and now we're ready now that's  pretty much it i want to focus on sheet 2 for now   so let's move ahead on sheet 2 what do i want to  do on sheet 2 i want to run an advanced filter i   want to determine the last row our criteria  is set based on formula so we don't need to   add any criteria here it's already based on  they're all based on formulas when it comes   to dates you really want to use a formula it's  much easier dates can create a real problem when   you add them through vba but when you create them  through formula and remember we really want them   unformatted we just want that then it works with  any type of regional settings or date settings   so really bad we know our criteria set from n2  through r4 then we just want the results here so   we're ready to go with that so let's do that with  focus on we're going to focus on wish sheet 2 now   with sheet 2. what do we want to do with sheet 2  i want to run the advanced filter for the current   month run advanced filter for current month and  so let's do for again we need to know the last   row the last booking row equals again we want  to make sure it's dot range we're going to use   we've already specified that was auto hotkey  again so that's y type dot range a that's   gonna get us the last booking row last booking  row once we have that we can run our advanced   filter just in case there's no data if the last  looking row is less than three then exit the sub   okay we can't if there's no data there's nothing  we can do so once we've checked that we can run   our advanced filter.range of course we're focused  on we need the headers in this case it's going to   be a3 all the way through j and the last and the  last looking row not not that value but advanced   filter i'm going to run that advanced filter and  what do we want to copy those to another location   and i want to know the criteria  range what the criterions criteria   range equals dot range just the n 2 through  r and 2 through r 4. that is a criteria range   not that value of course it's just the range  that we're focused on now that we have the   criteria range we're ready to get the copy  turn so copy to range colon equals what is the   colon equals what is the range dot range in this  case we're copying it through t2 just the headers   all we need to focus on all the way to aa2 all  the way through a2 let's just double check that   to take a look all the way through a2 just the  headers make sure these headers are exactly the   same in the criteria and the results they should  be exactly the same as they are in our original   data that's very important okay so once we have  that a2 and we're going to focus on unique now   unique uh colon equals and then false not so  concerned about whether they're unique or not   in this case okay so we've got our advanced  filter and now what we want to do is we want   to determine the last row of our advanced filter  and make sure that there actually is data so let's   check our last results row last result row is  equal to in this case dot range it's going to   be t column t is the one we're focused on  t this is our last results row and again we   want to make run a check to make sure if the last  result row is less than three then go to no data   and no data is just going to be at the bottom  of the fingering so let's just write down here   no data because there's nothing else we can do if  there's no data okay so moving on assuming that   there is data that we have results the first thing  what i want to do is i'd like to sort the results   i want to sort them by the room i think that  would be really helpful this way we have a nice   organized list so let's sort the sort the data by  the by the room number so how do we do that well   the first thing we want to do is clear the story  field so dot sort sort fields dot clear clear all   any concern sorts and now we're going to focus on  with sort so with dot sort what do i want to do i   want to sort based on what based on w3 based on  the room so we can do that with dots sort fields   dot add we're going to add a key to that what  is that key key is going to be equal to sheet   2 dot why am i writing sheet 2 because i'm focused  on with sort so i want to make sure we specify the   sheet in this case dot range w3 that is our key  that is the first one that we're going to focus   on and i want to sort on what is the sort on in  this case it's going to be equal to xl sort on   values sort on values i'm going to sort those on  the values and then order what is the order that   i want to focus on it's going to be equal excel  ascending i want them ascending because i want the   first room first and then growing ascending  and then the last i want the data option in   this case the data option we'