Search, Copy, and Paste

[mattp24]

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

 

[Scott Huish]

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]

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]

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]

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.