Copy Range from one sheet to another based on value

B

buzz71023

Active Member
Joined
May 29, 2011
Messages
295
Office Version
  1. 2016
Platform
  1. Windows
I have an inventory program with two sheets“List 1” and “List 2” with the exact same set up; 5 columns, in order Date (B),Product Code (C), Lot Number (D), Description (E), Location (F). The Location column has define locations ineach row/cell below. I am trying to getthe contents from List 1 to List 2 based on the Location value.. For instance I need the contents fromcolumns Date (B), Product Code (C), Lot Number (D), and Description (E) to ofR1A4_R1 on List 1 to transfer over to List 2 with the same locations. a simple copy and paste wouldn’t work becauseof the order of List 2 locations aredifferent. Once everything is transferredover to List 2, List 1 will be deleted.<o:p></o:p>
<o:p></o:p>
A sample of the order of List 1 and List 2 isas follows.<o:p></o:p>
Code: 
[FONT=Khmer UI][COLOR=#000000]List 1 Location order<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A1_R1        <o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A2_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A4_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A5_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A6_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A7_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A8_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A9_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A1_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A2_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A4_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A5_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A6_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A7_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A8_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A9_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A1_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A2_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A4_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A5_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A6_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A7_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A8_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A9_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI]<o:p></o:p>[/FONT]
Code: 
List 2 Location[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C1_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B1_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A1_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C1_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B1_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A1_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C1_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B1_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A1_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C2_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B2_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A2_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C2_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B2_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A2_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C2_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B2_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A2_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A3_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C4_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B4_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A4_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C4_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B4_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A4_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C4_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B4_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A4_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C5_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B5_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A5_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C5_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B5_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A5_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C5_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B5_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A5_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C6_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B6_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A6_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C6_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B6_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A6_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C6_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B6_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A6_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C7_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B7_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A7_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C7_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B7_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A7_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C7_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B7_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A7_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C8_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B8_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A8_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C8_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B8_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A8_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C8_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B8_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A8_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C9_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B9_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A9_R1<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C9_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B9_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A9_R2<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1C9_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1B9_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI][COLOR=#000000]R1A9_R3<o:p></o:p>[/COLOR][/FONT]
[FONT=Khmer UI]<o:p></o:p>[/FONT]
All help is appreciated and all input is welcome! Thanks<o:p></o:p>
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Are the names the same, because you could use vlookup
 
Upvote 0
The location values are the same on both sheets but are located in different rows/cells between the two sheets. I am not completely sure how I would complete that with vlookup? my first entry of data (under headers) is B6:E6, the location on that entry is F6 (R1A1_R1). On the destination sheet (List 2), That same R1A1_R1 location is located in cell F8. Which would mean The data from B6:E6 would now go to B8:E8 on the new sheet List 2.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
281
hagia_sofia
hagia_sofia
A
  • Solved
VbA - copying different colum ranges depending on which condition is met & pasting into different sheet
Replies
5
Views
1K
Fluff
Fluff
C
  • Solved
VBA - Copy and paste a range of values N times
Replies
6
Views
306
cydmm
C
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
347
kvsrinivasamurthy
K
A
  • Question Question
Macro to move certain columns to new sheet based on status (ie; "done")
2
Replies
10
Views
722
AnnyCav
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top