This is our overall flowchart for the grade manager project. In the previous two screencast, I've gone over how to create a directory for the course and how to import the roster file into your main project file. Now, I'm leaving out a lot of specifics, so you shouldn't just be taking what I'm doing and kind of putting it together. What I'm providing and the course files that I'm providing really just give you an idea of how to do these different types of things, but you are going to have to manipulate different things. A good way to do that is to step through these files using F8 and then you're going to have to make some modifications here and there. I mean you're going to have to do different things. So, now that we've created the directory, we've imported the roster file, we are now going to create the section rosters. So that's what this screencast is going to cover, how to create these section rosters. This is the roster file that you're going to be working with for the grade manager project. It's got a bunch of student names here, last name, first name. It's got the student ID and then it's got the section that those students are in. What you want to do next is to have your main file, your grade manager project, go through the roster, and it's going to separate all of the students that are in section 101 and place them in a new file. And same thing with 102, 103, 104, and 105. And you should make this so it's adaptable in case there's five sections here, but you want to make it so it's adaptable in case there's maybe 10 sections and maybe they are numbered differently, maybe 201 through 210. So you want to make your file adaptable. You also want to make it adaptable to different numbers of students. So our goal then is to create five new Excel files or.xlsx not macro enabled files. The roster files, the individual section rosters are going to just be regular Excel files.xlsx. So I'm going to show you in this screencast how you can do something similar, now this is not going to be directly on that roster file, but this should give you some good ideas of how to do that. I've got this file called CreateSections. It's got just some random names here and got numbers here, so these numbers are sort of to mimic the section number in the roster file. And what this screencast is going to show you how to do is the following. So once you have this done we can click on this button which is going to run the subroutine, and its going to ask the user. Please choose the directory where you'd like to place the files. So this assumes that a directory has already been made and you can see the previous screencast for how to do that. So then the user can navigate to where they want to, place that, and I've already made this folder here, so I'm going to select Section Files. Then it's churning through and it just went through, it did that rather quickly, it just went through. So now if I open up that folder, it's generated these five files. So it automatically detected that there were five different numbers here. We're assuming that those are only separated by their integer values and they're consecutive. That's an assumption we're making. And the extension here, underscore and the number corresponds to this. So, this is how you could go ahead and kind of do the same thing for the section numbers. Now what it also did was it moved the names. So if I open up the third file, I should see Sally and Stephanie. So, I'm going to go ahead and open up file three and you see that I've got Sally and Stephanie. And you're also going to want to transfer the student ID number. So yours is going to be a little bit different. So let me show you how we can do this. I'm starting with just a Sub CreateSections, you might want to call this CreateRosters or whatever you want. Whenever I'm working with multiple workbooks, which is what we're going to be doing here, I always kind of dim aWB as a workbook. That's going to be the active workbook when we open it. And tWB as this workbook, and right away I just set tWB equal to this workbook. The workbook that this code is in. Just so later on I can easily switch back and forth and I avoid any confusion. Then I have declared two new variables, minSection and maxSection, those are going to be the minimum of columns B and the maximum, respectively. So on my spreadsheet here, I'm going to search for the minimum and the maximum. So the min will be one and the max will be five. It just sort of gives you a span or a range of the sections that you're working with. I've also added two new variables and names. That's going to be the number of names, that's number of rows and sections, there is the number of sections and names. I'm just using the count A function to count the items in column A. So I'm going to count all the items in Column A and then I've got nSections which is just going to be the max section to find up here, minus the min section. So here will have five minus one, and actually I need to add one because five minus one will be four, but we've got one more, so we have five. I'm adding in another string here so this can be a string, dim folders. That's going to be the folder. And now I've added a message box, please choose the directory folder where you'd like to place the files. And this, as you've seen in the previous screencast, allow the user to select the folder or the directory that they want to place that item. So the items that were going to be creating the spreadsheets the new spreadsheets that are going to be creating. And then I need to end the width, I forgot that. Now we're going to enter into a pretty big For loop where we're iterating through all the sections. In this example, one through five. In general, if your sections can start with 101 and go to like 105, as an example, this is going to be different. It's going to be minSection to maxSection. So it's just something to keep in mind. But what we're doing here, is we're going to iterate through all the sections now and in each section or for each section, we're going to create a new file and we're going to extract the corresponding elements from the original spreadsheet and place them into that new file. Another thing I'm creating is this N vector. We don't know the size, the size is going to vary. And this N is basically just the names in each section and that's going to be inside of our iteration. So inside of our iteration for each of the sections, we're going to create a new vector here. It's going to be strings which is just going to be the names of the corresponding people that are in that section. Now the first thing I am going to do is we're going to re-dim N inside here, size one as a string. We're assuming that there is at least one student per section which kind of makes sense. That's an obvious conclusion. So we're going to re-dim that and we're going to keep going. When we're iterating through each of the sections, we have to enter into a second For loop where we're iterating down all of the items in our roster, or our names. So we have to iterate through and we're going to check for, for example, the first section will be one. We're going to go through and iterate. So, J is iterating from one to the total number of names and we're going to check to see if the elements in column B are equal to whichever section we are iterating through. So one, then two. If that's the case, then we're going to extract or keep the name to the left of it in the corresponding column in the same row, and that is going to be added to this N vector. I have forgotten to dim I and J as integers so I'm just going to add those. Now, what I've done here, so for the J going from one to N names, we're going to check to see it range B and J. So if J is one we're going to check to see a range B1 is equal to the current value of whatever we are looking through. So that's I. So I is one. We're searching for all the names in Section one. So if range B1 is equal to one, then we're going to keep that. If range B2 is equal to one, then we're going keep that name. So what I'm doing is I'm adding this K index. If we do find a match, then we're going to bump up K. It's also important here for the next iteration that we reset K to zero. So this K equal zero is really important. So once we find a match, we're going to bump K up by one. We're going to re-dim, Preserve N with that K size, and then we're going to say NK is the corresponding name that's in range in column A, corresponding to that row of the spreadsheet. So at this point, let's go through this. I just want to kind of step through this to show you what's going on right now. Let's go ahead and press F8. Oops, I need to define K, so I have dimmed K as an integer. So let's go through this. A lot of times when you have these dialog boxes and you're stepping through, after you step that line, it'll just automatically go through to the end. So I'm going to put a breakpoint here and let's do F8. So I go through here, we get the minimum section which is one, the max section which is five, we calculate the number of sections, we have a message box, please choose the directory, and then it opens up this file picker, and I'm going to click that, and now because I have that break point, now I'm going to enter into this For loop. So what we're doing is, now I is going to be one who were looking for Section one. We re-dim and now what we're doing is for all of the names, the names correspond to 1 through 11, rows 1 through 11, Jimmy through Shelley, that's what the J index is iterating over. So I'm going to say if B1 equals- we're looking for Section one right now, so I is one. And is that going to be true? No, we see the first match on row four for Section one. So we should skip through a couple times here two, threes, J is now four, so then we do have a match. We bump up K to one. Now we re-dim Preserve, Preserve keeps if we had anything there. Beforehand it's not important for the first item, but if we have a second item is going to be important. So then we set the first element of K equal to range. Now the corresponding element we found the first match in row four. So we want to extract the name which is in A4. So that's Max. If I open up N down here, we see that the first element of one is Max. Also, I forgot to mention this, but I have option Base one here at the top which is really important. Let me go through. Again, we're looking for Section one. We see the first match at line four. We see this next one at line eight. That's when J is going to be equal to eight. I can step through this a couple more time. J is seven. Now J is eight. We find the second match. K is now two. We ReDim Preserve N, which is our name vector. Now it's going to be size two. We're setting second element to Rodrigo. And I can open up N down here, and we see that we've extracted those. We've gone through for Section one and we've done this. At this point, after this next J, we're going to want to create a new file and export in to that file. But I'm going to go ahead and just make sure this is working now. I'm going to bump up to the next I. Now I is equal to two. We should find our first match for two, line six. When J is equal six. I'm just going to go through this quickly. One, two, three, four, five. We find six so we've added that. That name should be Joe which we looked here is Joe. I'm pretty confident this is working. At least it's creating our N vector. The next step then after here, let me go ahead and reset this, is to create a new worksheet and export this N vector for each of the sections. Let's go ahead and do that. We can add a workbook. Workbooks dot add just add a new workbook. I'm going to set that new workbook. That's going to be the active workbook as this variable AWB. Now I'm going to iterate through. I'm going to recycle this J variable. And I'm going to iterate through one to K. K again is going to be the size of our N vector for each section. For the first one, that's just two. When I open up that new workbook, when I add that new workbook, we're just going to say range A and J. J will be one is equal to the first position of J. J will be two. The range A2 is going to be the second component of vector N. This here just takes our N vector and outputs it to the new workbook. And the last thing we need to do, and I just recorded a macro for this. I don't remember the code for this. I just record to save as. We're going to just save that new file as a workbook. This is just an XLSX workbook. I had to manipulate how to change around this, the file path. So the file path is just a folder that the user has defined earlier. This was with this this dialog picker, this folder picker. And I just did that, concatenated it with. This is where you can define the file name. I just said file underscore and concatenate that with the I variable. Remember, this is still inside the I loop here which iterate through the sections. And then I'm going to I put that dot XLSX on the end. Finally, we're going to close that file. And because I just saved the file right here in the line above it, it doesn't really matter if this is true or false. It doesn't matter at all because you just saved it here. That should do it. Now let's go ahead and go through this. We know that that part's working so I'm going to put a break point here. Actually, I'm going to put the break point there. Let's go ahead and run this. I'm going to run this. We're going to choose our directory. I'll say my section files. Now we're going to iterate through one of these. I have just created a new workbook up here. You see here it has book six. When I go through this, we see that it's already placed max in here. Range A1 is equal to max. If I go to the next one here then it places Rodrigo there. Then what it does is it saves that file, I've already got a file in there because I didn't delete it the previous times. I'm just going to do that. That won't happen hopefully when you're running this. Then we close that file and we save. We don't save changes because we just make changes right up there. We can go to the next section two. We go through this. I'm just going to run to cursor down here. It opens up and creates a new file, a new book like this. Then we export Joe and Shelly and we save that. Again, I didn't delete this from earlier, so you shouldn't have that problem. It looks like it's working. I'm going to go ahead and pause this. I am going to go ahead and add these two lines, application dot display alerts equals false. In case you do have a situation where you're trying to override a preexisting file, you won't have that display, that alert that just popped up a few minutes ago. And also I'm going do new application screen updating equals false, so you won't see everything happening. You'll just see the final product. This is working. Let's go ahead and run this. Let me go back to my directory. I'm going to delete all of these files and I'm just going to press F5 to run. We're going to choose the directory. Section files. Click OK. And it goes through. Just after a few moments, it's done and I can open up that folder. For example, I can open up file four and we see that we have Jimmy and Dana. And if I go back to my original, we see Jimmy four and Dana four. And if I open up the other files we would see similar things. This screencast has shown how you can sort a roster file into section piles. You can create those files, store them and save them for later use. In the next screencast, we are going to open up each of these files so we're going to automate. We're going to open up each of these files. We're going to add the headings onto here. The first thing we're going to do is have to move these down or insert a row. Then we're going to add the headings, the different assignments. Thanks for watching.
