Step through range x, copy and paste columns in range y based on values in range x

mac8832

New Member
Joined
Jul 13, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you in advance for your consideration and help. I am new to the message board and hope I find a VBA solution to the following:

I want to step through values within range x in worksheet1 to the extent the range contains numerical values (end loop after last row of data is passed). When within the loop, I want to lookup range y in worksheet2 based on the cell value in range x, then copy and paste range y to worksheet3. When pasting range y to worksheet3, I do not want to overwrite existing data, but paste to the right of the last column with data. For each value in range x in worksheet1, there are three columns in worksheet2 to copy and paste to worksheet3 (i.e, 3 in range x corresponds to 3x 3's in range y).

range x in worksheet1:
1626202711402.png



range y in worksheet2:
1626202776732.png


copy/paste to worksheet3:

1626203094869.png

The left border on column D indicates where the first column of data is pasted. As I had mentioned above, 3 columns in range y will be pasted per 1 value in range x. Once pasted, the next 3 columns in range y corresponding to the next value in range x will be pasted to the next three columns to the right.

Thank you again.
 

Attachments

  • 1626202992363.png
    1626202992363.png
    1.7 KB · Views: 4

mac8832

New Member
Joined
Jul 13, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
DanteAmor,

The above code you provided is precisely the solution I needed. Thank you very much for your expertise here.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,632
Office Version
  1. 2007
Platform
  1. Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,141,774
Messages
5,708,458
Members
421,570
Latest member
BaileyJ

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