VBA to copy and match to master list


Active Member
Jun 15, 2005
I have a list of names and scores (Col A and B)

The list of names is a subset of the master list of names on another sheet.

Each time I run an event I want to copy the score for each name to the respective name in the master list and the next column. There is a new column for each event.

The subllist of names will vary with each event but will always comprise names that are in the master list.

Top 3 rows of each new column in the master list contain the event date, location and name.

For each name in the sublist, the code needs to copy the score for that event and name, and then paste it into the respective name in the master list, in the next most right column corresponding to the event column.

Can anyone help me in my quest?

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CompareSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#007F00">'Determine the last column used in the Master</SPAN><br>    LastCol = Sheets("Master").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column<br>    <br>    <SPAN style="color:#007F00">'I needed a little more info here as to where the name of the event was found on the "Event" sheet</SPAN><br>    <SPAN style="color:#007F00">'I made the assumption it will be just like the Master "Top 3 rows" in order of "date, location and name"</SPAN><br>    <SPAN style="color:#007F00">'So this looks for the Event name in A3</SPAN><br>    <br>    <SPAN style="color:#007F00">'Next, identify which column in the Master is the correct Event</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> LastCol<br>        <SPAN style="color:#00007F">If</SPAN> Sheets("Master").Cells(3, x).Value = Sheets("Event1").Range("A3").Value <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'x now becomes the column in the Master that matches A3 in the Event</SPAN><br>            EventCol = x<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> x<br>    <SPAN style="color:#007F00">'Next Match a name in the Event to the Master List</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> a = 4 <SPAN style="color:#00007F">To</SPAN> Sheets("Event1").Range("A65536").End(xlUp).Row<br>        <SPAN style="color:#00007F">For</SPAN> b = 4 <SPAN style="color:#00007F">To</SPAN> Sheets("Master").Range("A65536").End(xlUp).Row<br>            <SPAN style="color:#00007F">If</SPAN> Sheets("Event1").Range("A" & a).Value = Sheets("Master").Range("A" & b).Value <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'Finally in the Master, write the score for the person in the next column over from the event</SPAN><br>                Sheets("Master").Cells(b, x + 1).Value = Sheets("Event1").Range("B" & a).Value<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> b<br>    <SPAN style="color:#00007F">Next</SPAN> a<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
Last edited:
Upvote 0
Thanks a lot VHS01 - I was kind of heading in the right track but seeing your code it would have been a long way to go for me (VBA L plater).

Ive used your code and added a few bits and used the right cell references for the master sheet ("Roubaix") and the Event1 sheet ("Race sheet").

I get a Type Mismatch error which I cant trace -was hoping it might be obvious to you??

Anyway thanks for the help,

Here is my finished code
Sub SubmitResults()

Dim LastCol As Integer
Dim EventCol As Integer
Dim a As Integer
Dim b As Integer
Dim x As Integer

'Determine next available column in Roubaix
LastCol = Sheets("Roubaix").Cells.Find(What:="*", After:=[A9], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'Identify correct column in Roubaix that matches event
For x = 1 To LastCol
    If Sheets("Roubaix").Cells(3, x).Value = Sheets("Race sheet").Range("c2").Value Then
    EventCol = x
    Exit For
    End If
Next x

'write event course and distance info to event column
Sheets("Roubaix").Cells(4, x + 1).Value = Sheets("Race sheet").Range("c3").Value
Sheets("Roubaix").Cells(5, x + 1).Value = Sheets("Race sheet").Range("e3").Value

'Match rider name from Race Sheet with Roubaix master list
For a = 7 To Sheets("Race sheet").Range("a65536").End(xlUp).Row
    For b = 9 To Sheets("Roubaix").Range("a65536").End(xlUp).Row
        If Sheets("Race sheet").Range("a" & a).Value = Sheets("Roubaix").Range("a" & b).Value Then
            Sheets("Roubaix").Cells(b, x + 1).Value = Sheets("Race sheet").Range("k" & a).Value
    Exit For
   End If
  Next b
Next a

End Sub
Upvote 0

I have been sitting here for awhile trying to create a "dummy" workbook to simulate yours, but I have too many questions. I think I can help you alot easier if I see the actual workbook.

If you can, sanitize the data and email me the actual book with any other instructions, it probably won't take me long to finish it up.

Lets use the site email function to keep our addresses anonymous.
Click on my name to get to the info page and under the "Contact Info" tab select "Send a private message to VHS01". Once I hear from you I will reply with an email address where you can send the actual book.

For any others interested in this thread, I will post the answer back here.

Upvote 0
I made a dummy workbook and your code seemed to work fine.

On the dummy, the only change I made was how to find LastCol:

LastCol = Sheets("Roubaix").Cells(3, Sheets("Roubaix").Columns.Count).End(xlToLeft).Column

After that, your code worked fine.

If you still have problem email me as described above.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back