VBA script to copy and order strings based on a key value, code doesn't seem to run

I have 2 files. In file 1 I have certain strings in column A. In column D, I have team numbers in the format Team 1 (example). The teams go from 1 to 19, and the order of teams is randomized.
In file 2 I have team numbers in column D, except here the team numbers are formatted as Team_361948_1_TBL2324. The number 1 indicates that this is team 1. This is about the same teams as file 1, just a different notation. Here, the team numbers are ordered from team 1 (top) to team 19 (bottom). Every team is supposed to have a maximum of 7 members, so in column D of file 2 you first have 7 times Team_361948_1_TBL2324, then 7 times Team_361948_2_TBL2324, etc.

What I want to do is copy the student numbers from file 1 to file 2, except I also want them to correspond to the correct team indicated by column D. So the order of the student numbers needs to be changed based on the team they belong to, as indicated in file 1. I'm trying to do this with a VBA script, but I don't really know what I'm doing. Could anyone please take a look at the code I have so far and tell me what I'm messing up here? Thanks in advance! Code is attached.

see big yellow banner - upload your actual workbooks (de-sensitized if necessary) - you are likely to get an in-context solution as we would be able to test which we cannot do with just a text file.

Place this macro in File2. Make sure that both workbooks are open. Run the macro.

Please Login or Register  to view this content.