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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What code are you currently trying?...would help to post it!
Why are the rankings on the Master different to the working ??
AND
Are the names and placings already on the working sheet and you only want their subjects ??
 
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Hi Michael,

I can’t copy and paste the code over because my company intranet doesn’t allow access to the internet. I posted this question on my personal device.

The code I attempted to use was on creating an array and inputting details to array, that didn’t work out so well because I had so many errors that didn’t allow for me to run the code. At one point I managed to copy over the values but it didn’t copy the correct rows over and after more tweaking it got destroyed and became an error landslide…I can’t remember the one that worked. Sorry 🥲
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, so what about my other questions ?
From Row 8 of Master:
1. Column D to be copied to WorkingCopy between Column A and Column B......so a column needs to be inserted in the Working sheet?
2. Column F, H and J to be copied to WorkingCopy Column G, H and I.....if the Names are alerady on the working sheet in the same order, the entire column of data can be copied across ??
 
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

The rankings are basically known as their banding. Banding is determined by how well they perform at their entry exams.

The working copy ranks them according to their scores in their final exams, the final scores are not shown in the master copy but is in columns G, I and K of the working copy. Those are their scores for their choices.

and

Yes, the name and placing are already on the working copy.
 
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, so why not simply make a copy of the master sheet and delete columns G, I and K?
 
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Ok, so what about my other questions ?
From Row 8 of Master:
1. Column D to be copied to WorkingCopy between Column A and Column B......so a column needs to be inserted in the Working sheet?
2. Column F, H and J to be copied to WorkingCopy Column G, H and I.....if the Names are alerady on the working sheet in the same order, the entire column of data can be copied across ??
1. Yes the column needs to be inserted between A and B.

2. The names can be in very different orders every batch of students. Which means in another year intake, Alex who was a top scorer for his entry exam, may end up falling to the bottom ranking in his final exam. So if I copy the entire column it might not reflect it.
 
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
OK, so why not simply make a copy of the master sheet and delete columns G, I and K?
So, I’m given the working copy by another department that consolidated and ranked them according to their final score. I only have access to the master copy and not how the department churns out the working copy. Therefore I am required to input the required details for my department to use the working copy.

the master copy does not have their final scores, only their entry scores.
 
Joined
May 20, 2021
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Sorry let me rephrase myself as I think there’s a confusion here. Give me a few mins to post a reply that clarifies all the confusion.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So, first stab at this
VBA Code:
Sub MM1()
Dim lr As Long, Ms As Worksheet, wc As Worksheet
Set Ms = Sheets("Master Copy")
Set wc = Sheets("Working Copy")
lr = Ms.Cells(Rows.Count, "E").End(xlUp).Row
Ms.Range("F9:F" & lr).Copy wc.Range("C2")
Ms.Range("H9:H" & lr).Copy wc.Range("D2")
Ms.Range("J9:J" & lr).Copy wc.Range("E2")
End Sub
 

Forum statistics

Threads
1,136,734
Messages
5,677,447
Members
419,693
Latest member
divtjd

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