This screencast is going to build off of the example we worked through earlier where we worked with employee schedules. Earlier, we worked with a workbook that had different worksheets for the different employees. But in this example, I am going to want to consolidate the schedules from different workbooks. And to make things a little more challenging, I have different numbers of schedules or worksheets, tabs, for each of the employees on different workbooks So, on one of the workbooks, I have just one employee. On the second one, I have three employees. And on the third worksheet, I have two employees. So, we need to make this subroutine such that it can adapt to different sizes of files and different numbers of files. You'll find these workbooks in Employee Schedules.zip and you can use these to work along with me. So, I'm going to go ahead and close those other spreadsheets here, these other workbooks, and then what we're going to be creating in this screencast is the following. We're going to be creating this workbook where we've got a subroutine where we can consolidate schedules. When we do that, it's opening up those three different files, those files that are in the Employee Schedules.zip folder, and it does exactly the same thing that that previous screencast that worked with consolidating employee schedules on different worksheets does. And then we can reset this. So, that's we're going to be creating in this screencast. So, I'm starting with the consolidate schedules subroutine that we created in that previous screencast. I just copied and pasted it into a new workbook and we're going to make some adaptations to this. The first adaptation, I am adding a folder as a variable that's going to be a string and file name because we're going to be working with different directories and file names and workbooks. So, I've got aWB, tWB, both as workbooks. After my Dim statements, I'm setting tWB equal to ThisWorkbook just to make it easier to switch back and forth between different workbooks and to avoid confusion. Next, I've set this folder. This is the folder path, this is the file path. So, that's where my test folder here where my files are, so I right-click on their copy address and then you can paste it here. So, that's how I got that. And then we're going to set the first file name equal to the first Excel file in this folder up here. Please see the screencast on opening workbooks to understand kind of what I'm doing here and what I'm going to be going through in the next little bit. Now, in this case, we're going to be iterating through all worksheets of all workbooks. We don't have to worry about this part if the name is not Summary because we're only opening up the files that are in this directory. Assuming that the ThisWorkbook is not in this directory, then we can do that. But if ThisWorkbook is in, for whatever reason, we've saved ThisWorkbook in this directory, then this won't work. So, I can eliminate this part. I'm also going to eliminate the End If. And what I'm going to do is place this entire For Each Next loop in a Do loop. The reason for this is we're going to loop over all workbooks that are in this folder. Again, see the screencast I have on opening workbooks for what I'm doing here. The first step then is to open that first file name here. So we're concatenating the folder with the file name. Then I'm going to set aWB equal to the ActiveWorkbook. Then outside the For Each here, but still inside the Do loop, I'm going to close the active workbook and save changes. We're going to make that false, and then we're going to obtain the next file name. If we just do Dir, that's going to take the next file name in our folder, our directory, up here. This Do loop iterates over all files in the folder that we specify up here. So, we're going a loop until we're out of names. And in that case, we're going to exit the Do loop. Outside the Do loop, I'm going to activate this workbook, and then the rest of this is exactly the same. We're going output and make sure that on your file that you're working with, you have named this Summary. So here, I have a summary. I just copy this from the previous screencast spreadsheets. So now that we have that worksheet called Summary, I'm going to export then into range B4 to F12 of the summary sheet that array s that we've built here. That's counting the number of Xs that we have total in all the workbooks and all of the work sheets. So, I think we're ready to see if this works. I have assigned to this button this consolidate schedule's subroutine into this reset button. I've assigned the same reset button down here that we had earlier. So, I haven't done anything to that, and I haven't done anything to the lower section of the consolidate schedule's subroutines. So, this should all work the same assuming that we've named the tab Summary. So, let's go ahead and see if this works. I'm going to put a couple of break points in here. A couple other break break points in here. So, I'm going to go here, click F5, and now we enter into here. We're going to open up that first workbook. So, we've got that workbook open here, that's Charlie, and we are then going to iterate through there. So, for each W in worksheets, there's only one worksheet in this workbook. And then we iterate through here. We're going to iterate through all the cells of range B4 to B12, searching for Xs and counting those. I was going to move this break point and I'm going to run to cursor, and then we can close that workbook, and now we go to the next workbook that's in our directory. We go back, we open up the next workbook. So, this is the second one. This one has three worksheets on it. So, we're going to be doing quite a bit more iteration here because now, we have three workbooks. But anyway, this is what's going to happen, pretty confident is working. So, I'm going to go there, do run the cursor, and it finishes, and then we activate this workbook, and then we export, and then I'm just going to press F5 to resume because then we search for the max and we highlight the max. And also, our reset button works. One last thing I'm going to do here after the Dim statement, I'm going to put this line Application.ScreenUpdating equals false, so we can't see anything that's going on. And I'm just going to close this and I'm going to show you what this looks like to a user. So, they are just going to get this and they will consolidate schedules and it goes through. And you notice there wasn't any flashing or anything because of that screen updating equals false. So this is just the result. And each week then, or each month, or each year, whatever, the employees could put their files into this directory and you could rerun this. You could also have a directory like a Dropbox. I have Dropbox over here that's linked into the web. So, you can have different people put files into here. As a directory in VBA, you can use Dropbox. So, these things can be kind of updated automatically in the cloud, essentially. You can do some really cool stuff with what I've shown you in this screencast. Thanks for watching.
