Copy Specific Column from Mastercopy to WorkingCopy based on Row Value

[XanderTheNotSoAwesome]

As seen in the image example.. (I am a total VBA noob as evidenced from my continuous requests for assistance in this forum, apologies. I tried doing it myself but ended up always failing after hours of effort..

I require assistance in a macro code that helps me to copy over from MasterCopy sheet to WorkingCopy sheet: (The dummy columns and rows are accurately placed as according to my actual data sheet)

From Row 8 of Master:
1. Column D to be copied to WorkingCopy between Column A and Column B
2. Column F, H and J to be copied to WorkingCopy Column G, H and I.

I can't seem to match the column values to their specific rows when I copy the columns over.
(Example: Ben is ranked 2, with his 3 choices being History>English>Math but when I try to copy over using my own codes, Ben gets David's values - Rank 1, Science>History>Math which is NOT what I want)

The actual data sheet is bigger than this, I can have more than 50 students to sort out sometimes thus I resorted to using VBA, though I suck at this.

Any experts willing to assist this noob who is lost and confused?

 

[XanderTheNotSoAwesome]

So MasterCopy and WorkingCopy is given to me as it is.

The blanks on MasterCopy are as follows:
1. Columns A to C are just personal details.

2. Column G is their entry score for Choice 1, I, entry score for Choice 2, and so on for choice 3.

3. As for what’s the difference between Placing and Ranking, Placing is basically who is the top scorer and so on so forth, 1st place to last place. Ranking is their banding, we have a four bands that we stream students into based on their entry scores. Band 1 for excellent students, Band 2 for above average, etc etc.

The MasterCopy is given to us at the start of the course.

After completing the course and the test department consolidated their final scores. They send us the working copy.

The WorkingCopy is arranged from the top scorer to bottom.

1. Column C, D, E and F are their scores from their final exams based on the subjects they were given.

What I was tasked to do:

1. Add their rankings into the WorkingCopy based on MasterCopy.

2. Add their choice 1-3 to the WorkingCopy columns G to I.

 

[XanderTheNotSoAwesome]

Your first stab was much more lethal than my 100000th stab. It works, but the choices are not rearranged according to each students.

Whats happening is that Ben is getting David’s choices because David was the 2nd student in the MasterCopy. But Ben is supposed to have his choices according to his own row (5th student) in the MasterCopy.

So basically what I had in mind was to tag the Ranking, Choice 1, Choice 2 and Choice 3 to the individual names in the MasterCopy.

Then paste them according to the tagged names in the WorkingCopy. If you get the idea?

 

[Michael M]

Ok, try this one

Sub MM1()
Dim Ms As Worksheet, Ws As Worksheet, r As Long, t As Long
Set Ms = Worksheets("Master Copy")
Set Ws = Worksheets("Working Copy")
lr = Ms.Cells(Rows.Count, "D").End(xlUp).Row
lr2 = Ws.Cells(Rows.Count, "B").End(xlUp).Row
For r = 9 To lr
     For t = 2 To lr2
        If Ms.Cells(r, 5) = Ws.Cells(t, 2) Then
            Ms.Cells(r, 6).Copy Ws.Cells(t, 3)
            Ms.Cells(r, 8).Copy Ws.Cells(t, 4)
            Ms.Cells(r, 10).Copy Ws.Cells(t, 5)
        End If
     Next t
Next r
End Sub

 

[XanderTheNotSoAwesome]

You are a genius! Your code was really simple to understand and works perfectly!

As for the ranking, is there a code to add the ranking column between Column A and B? With the same idea.

 

[Michael M]

You mean like this

Sub MM1()
Dim Ms As Worksheet, Ws As Worksheet
Dim r As Long, t As Long
Set Ms = Worksheets("Master Copy")
Set Ws = Worksheets("Working Copy")
Ws.Columns("B:B").Insert
lr = Ms.Cells(Rows.Count, "D").End(xlUp).Row
lr2 = Ws.Cells(Rows.Count, "C").End(xlUp).Row
For r = 9 To lr
     For t = 2 To lr2
        If Ms.Cells(r, 5) = Ws.Cells(t, 3) Then
            Ms.Cells(r, 4).Copy Ws.Cells(t, 2)
            Ms.Cells(r, 6).Copy Ws.Cells(t, 4)
            Ms.Cells(r, 8).Copy Ws.Cells(t, 5)
            Ms.Cells(r, 10).Copy Ws.Cells(t, 6)
        End If
     Next t
Next r
End Sub

 

[XanderTheNotSoAwesome]

Solved, thank you so much for your time and effort to inquire more to help better.

Very well appreciated, you darn genius!

Have a great day ahead!

 

[Michael M]

Cheers, glad to help...,