Copy Specific Column from Mastercopy to WorkingCopy based on Row Value

Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Attachments

  • Master.png
    Master.png
    15.8 KB · Views: 5
  • Working.png
    Working.png
    11.8 KB · Views: 6
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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?
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, try this one
VBA Code:
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
 
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

You mean like this
VBA Code:
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
 
Solution
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,136,731
Messages
5,677,420
Members
419,692
Latest member
Also_Confused

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
Top