This screen cast is going to sort of quick it all together which you've learned so far by worksheets. I've got this file employee meeting times starter on the website. And what we're going to be doing is in each of these six tabs we've got six different employees. Each employee submits sort of their availability for a joint meeting time. So, for example, Jim could meet during these times where there's an X. So, each of these six employees does this. And what we want to do then is automatically go through here and count the X's in the corresponding ranges, so the days and times. And then we want to tally to see what the best time to hold a meeting is. I can click this button, consolidate schedules. It's going to go through and count the items, the X's in the following six sheets and determine the best time. It's then going to find the maximum, and it's going to highlight that green. So the user can just say, Tuesday from 1 to 2 is a great time to meet, or Friday from 11 to 12 is a great time, and then we've got a reset button on here. So that's what we're going to be making in this screencast. So I've created a sub-routine called consolidate schedules. I'm going to draw a quick flow chart for the general process of what we're going to be making. So, I'm going to iterate through all worksheet, W is going to represent the worksheets. Inside each worksheet we're going to iterate over all I and J, which are going to be the rows and columns of the Area that contains all the Xs. In other words, on our spreadsheet we're going to iterate through this region here for each of the work sheets. And this should actually be W = 2, we're going to iterate from worksheets 2 and tell how many of them me have. And what I'm going to actually do in reality in the code is a little bit different. So inside each worksheet we're going to iterate through all the rows, that's index i. We're going to iterate through all columns of each row, that's index j. And I need to dim the following variables. W is going to be dimmed as a worksheet, i, j, nr and nc all as integers. So when we're searching through each cell of each worksheet, we're going to be checking to see if there is an X there. If that's true, then we're going to add one to a central array. So we're going to be creating kind of a central array S, and S is going to be a 9 by 5, this should actually be only 5. So whenever we iterate through, and we find an X on the individual worksheets, we're going to add one to the tally. So this obviously starts out with a bunch of zeroes. But when we encounter an X, we're going to add one to that particular ijcombination. And so this might become 1. And then at some other point, in another worksheet, if we encounter another X, then we're going to add one to that so this might become 2. And in that way we're sort of tallying the number of Xs total in our nine different, categories for time and our five different days. So if that's true, if we encounter an x in any individual worksheet, we're going to add one to our central array S. If it's false, we're not going to do anything. So we keep going and going and going through all the worksheets. When we're done, we created this s array that tallies the total number of corresponding X's and then we output at the very end. And the last thing we need to dim is S, we're going to dim that as an array of integers. So let's go ahead and put this into VBA code. First we need to dim w i j nr nc and s as an array. Because I'm working with arrays I'm going to add the option base 1. Now I know that I have a 9 by 5, so I'm just going to say the number of rows is 9, the number of columns is 5. I'm dimming S as unknown size, although I do know the size to begin with, but I'm just going to leave it like this. I'm going to redim S now. And now we are ready to enter into the first for loop, where we're iterating over all Ws. So I'm going to use a for each for this. For each W in worksheets, that should be worksheets, then we enter into our outer for loop for i, our inner for loop for j. Inside the inner for loop we're going to check to see if the individual cells have a X in them. So I have added this line here, this if then statement. If w.Range, the range B4 to F12, so that corresponds to this range. If that cell (i, j) of that range is equal to a capital X, now make sure you use a capital X, it won't work if it's a lowercase x. Then S (i, j), we're going to add 1 to that particular component of our S array, and then we're going to keep going. Once we're done iterating through all worksheets, we're going to output S, so that's the last thing that we need to do. So we can do worksheets summary, that's the name of it, .range B4 to F12 equals S. So what that's going to do is on the summary sheet, it's just going to export that to here. I've assigned to this button the consolidate schedules sub-routine. We're also going to make a reset sub-routine. The reset sub can be shown here where we're just, so for each cell in range B-4, F-12 of our summary worksheet We're just going to clear that range. Now one thing we haven't done yet is we're actually iterating through the summary worksheet when we go through this. So what I'm going to do is these two, for loops I'm going to put into an IF statement. So the IF statement is, If Not w.Name = Summary then we do the iteration. This means that for all worksheets that are not named summary, we're going to count. So we're just sort of eliminating the first summary sheet from consideration when we do this. So I think we're ready to see if this works. I'm going to go ahead and press f8 to step through here. We redim S, we go through the workbooks. The first workbook is called summary so we skip it. We go into the second one, and now we enter into our counting. So, if I open up S down here in the locals window, the first row. This seems right because the first row of S we should have a couple of check marks here, which we do, and so it looks it's working. And now I'm pretty sure it's working so I'm just going to put my cursor our here and do debug run to cursor. We can then reopen S down here, and we see that we've counted a total, the total number of check marks, the X marks in our six worksheets. Finally, we output that result into our summary file. So this contains all of the information and the availability that the number of employees that are available at those different times. The last thing we're going to do is at the very end of this subroutine, we're going to find the maximum of this range on the summary worksheet. We can also verify that our reset button is working, which clears everything. So I forget how to highlight the cell green, and I'm just going to record a macro. I'm also going to record for how to make this no color, because when we reset we want to actually reset the colors too. So I've redone that and this is the code that we get, so we can make it green, and this is how we can clear the formating. So we're going to use this in our reset sub-routine. So to find the Max, I'm going to first dim mx as an integer. Mx then is going to be, I'm going to use the worksheet function Max to find the Max of S. Then what we are going to do is we are going to highlight the Max cell green. So I've taken the code that we recorded in that macro for each C in Worksheet Summary in that particular range. We're searching through it, and I change this instead of selection that was recorded in the macro. I've replaced that with c. So, if c.value equals the maximum then we're going to make it green, otherwise we're going to make everything just have no formatting. The reason I'm doing this is in case the user forgets to reset, if anything already had green in it is going to remove that green. In the reset I'm also going to clear all formatting using what I got using the second part of that macro recording. I also have to dim C as a As Range. So now I think we're ready to go. I'm going to go ahead and press Consolidate Schedules. We see that we've looked through, we've counted the various availabilities. We've searched through for the largest which is going to be 5 in this case, and where we have a 5, we've highlighted it green. And the reset button clears everything, it also clears the formatting. So this sort of shows a kind of a complex example of how we can use the stuff you've learned about worksheets to do something useful. Thanks for watching.
