We're then going to switch back over to this workbook, the main file. We're going to make sure that sheet one is activated. There are a couple different ways to do this, but I've decided to use the match function in Excel, so we can use the match function to look for the name that we obtained earlier. Sally for this first example and we're going to search through a range A2 to A12. What that's going to do is we're going to search through this for Sally and that's going to give us, it'll tell us the index number or the match number, so that be 1, 2, 3, 4, 5, 6, 7, 8, 9. So IDX should be 9. We're getting there we just have a few more steps. If we do Range C and IDX plus 1, remember in our example for Sally index IDX was 9, that's C10. So we're going to take Range C10. And remember when we run through this this is actually going to be empty but we're going to take C10 and that's going to be the active cell. So we're activating, we're selecting that cell, you could also use activate. So that's going to be now our active cell. And now we just export we're going to go through for J equals 1 to n items. In this example n items is just three because we have A, B and C and we're going to then do ActiveCell.Offset. We're just doing the same row, so we're not offsetting any role you know we're just going to go from 1 to 3 that's going to be active cell offsetting 0 to 2 and that's just G row 1 column J. So that's going to take G that we've already stored from earlier and is going output it into this row here and finally we just have two more lines AWB, we want to close the active workbook, that's one of the files, the grade files that we opened. And you can either do true or false we're not making any changes, so it doesn't matter. And then we need this filename equals DIR. What that does is it moves to the next file name. And we loop until we're out of filenames. At the end, we might just want to put a confirmation message, maybe something like files synced. And I'm going to do one more thing up here which I like to do. Application.ScreenUpdating = False. The user doesn't see the process, they just see the final result of this subroutine. If we go through this, I forgot to dim nStudents, that's why we step through this using F8, so I'm going to do that. I also forgot to dim I. And also name and also index. Boy I really messed up here. Okay, so now we go through this and it looks like it's working. And I do have one error here. This FileName = DIR should not be there. So let's go ahead and do a side by side, I'm going to show you how we can step through this and see what's happening on the spreadsheet. So stepping through this we go through here. Turn off the screen updating although I think in debug mode we can still see the screen updating hopefully we're going to set the path. So you notice down here in the Locals window, we're setting the path then we're going to activate sheet 1 which is already activated. We're going to then count the number of names, so we did that we got 11. There's 11 names here in column A and items and we count the number in this row and the number of items is 3. I can keep going here I'm re dimming, I'm setting the file path and then we're entering into the do loop. What we're going to do is we're going to open up the first of the files. So now we've opened up the first of the files we're going to set that equal to the active workbook AWB we're going to make sure sheet 1 is activated just in case it is not and then we're going to count to the number of students, using that count A function in column A. So the number of students down here is 2. We're going to select range A2, we're then going to activate make sure the active workbook is selected that's important not the first time through but later on because see here we go TWB, so when we go back around we're going to have to reactivate the active workbook then we are going to get the name, so that's the first one we should get Max which we do down here. And then I can create the vector G. So the vector G is B2 to D2, for when we're looking at Max here. And I can open up the G vector down here and we see that that's 2, 3, 8 which makes sense. We then go back to, we activate this workbook and you see it switch back over to the main file here. We look up using this match function where do we see Max in column A, that's essentially what we're doing. So that is the 1, 2, 3, 4, 5, 6, 7th item. So IDX is 7 we're then going to select range C8, so we're right there for the for Max, that's exactly where we want to be. And now we just iterate through the three items. We're going to iterate through and we're going to offset. So 2, 3 and 8 we just put in the adjacent cells. So 2, 3, 8. And then we're going to do is we're going to go to the next student in that file and that is Rodrigo. And you see down here and the locals window we picked up Rodrigo, we obtain the G vector, we then go back to this worksheet, this workbook and we export so 9, 1 and 5. And then we're going to close that workbook, we bumped to the next file and we loop and we do the same thing again. So I'm just going to go down here, put my cursor there and I'm going to do run to cursor, it's going to open up the other four files and do the exact same thing. Then when it's done we see the result and we can just run our final line of code. That says the files are synced. So this is how you can import and sync the different roster files that you're working with in the Grade Manager project. You can base a lot of it off of what I did here but you're going to have to make some modifications, especially because you're going to be using A, which is going to make things a lot more complicated. Thanks for watching.
