Search, Copy, and Paste

mattp24

New Member
Joined
Jul 24, 2007
Messages
5
I need to create something (I assume it will have to be a Macro) where I can:

1. Enter the three digit code on Worksheet2
2. The Program then finds this code in that cell and locates it on column B on worksheet1
3. Then the Program copies that row and then next ten below it
4. Finally the Program pastes this data into a cell / range of cells on Worksheet2.

Any ideas, I would be extremely thankful if someone could supply me with the code for this. I don't know if this is the best way to do what I am trying to accomplish but I think this would suffice. Thanks.

mattp
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The Program then finds this code in that cell and locates it on column B on worksheet1

Will this code only appear once in Column B or does it need to look for multiple occurrences?

Finally the Program pastes this data into a cell / range of cells on Worksheet2.

Did you have a specific place in mind?
 

mattp24

New Member
Joined
Jul 24, 2007
Messages
5
It will only appear once in column B. It is a ticker name for a company (for example MMM). And then I want to paste the dividend data from that ticker company (appx. the ten rows below the title name title) to any cell in worksheet2 (for example cell C4). Thanks
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are you not not wanting to copy entire rows? What columns are your rows using (i.e. Columns A through J)? Or are you wanting to copy just the values from Column B?

I still don't understand what you mean by pasting into any cell. Surely, you don't want me to randomly pick a spot on the spreadsheet to paste it.
Perhaps you should pick a spot, say first available row in Column C for example.
 

mattp24

New Member
Joined
Jul 24, 2007
Messages
5
Yeah, I want to copy columns D-K. The row with the ticker name (the item I want to search for using Vlookfor or whatever) is the first row (trying to search for the row), first column (column D). So I want that row and the 24 rows below it, columns D-K.

Now I want to take this data and copy it and then paste it into a different worksheet (worksheet2). I want to paste this information into B6:I30 (rows 6-30, columns B-I).

Now the item that I will be looking for (the ticker name) I want to input into F4 on worksheet2.

So I imagine this is how the sequence would go:

- worksheet2, input ticker name in cell F4.
- worksheet2, click macro button
- Macro search for this ticker name in column D of worksheet 1.
- Once found, the macro then copies and paste the row of the ticker name (columns D-K) along with the 24 rows below it.
- The Macro then takes this data back over to worksheet2 and pastes it in cell B6 (B6:I30).

I hope this helps, and again thank you so much for doing this. I have been trying to work on it all day and have learned that I am a terrible VBA programming.
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

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