Macro to Search and Copy

[davidnagel]

Hello there, I am looking to create a macro that does the following;

1. Copy data in a cell from a workbook
2. Paste into Find in the personal.xls
3. Find the data that matches
4. Copy and paste a range from the personal.xls
5. Paste into the first workbook
6. Repeat for a range of cells

Any ideas?

 

[Bernie Deitrick]

David,

For example, this will find the values from cells A2:A11 of the active sheet, find those values on
sheet1 of persaonal.xls, and copy the 5 cells immediately to the right of the found cells, and paste
the values into the cells starting in column B of the active sheet.

Dim myC As Range

Dim myC As Range
For Each myC In Range("A2:A11")
Workbooks("Personal.xls").Worksheets("Sheet1"). _
Cells.Find(myC.Value).Offset(0, 1).Resize(1, 5).Copy myC(1, 2)
Next myC

Or, be more specific about where to search:

Dim myC As Range

Dim myC As Range
For Each myC In Range("A2:A11")
Workbooks("Personal.xls").Worksheets("Sheet1"). _
Range("A:A").Find(myC.Value).Offset(0, 1).Resize(1, 5).Copy myC(1, 2)
Next myC

You could also use VLOOKUP formulas to do the same.

HTH,
Bernie
MS Excel MVP

 

[davidnagel]

Bernie, thank you for your reply, bear in mind, I intend this macro to run
from the personal.xls on the open workbook, would some code need to adjusted
to accomadate this?

 

[Bernie Deitrick]

David,

I wrote it to work from any workbook, to act upon the currently active sheet, as long as
Personal.xls is open. Also, I did not include any error checking, in the case that the value in
cells A2:A11 is not found - would the value ever not be found?

If you want to run it from Personal.xls, you could change

Workbooks("Personal.xls").

to

ThisWorkbook.

And also, for a little more flexibility, change

For Each myC In Range("A2:A11")

to

For Each myC In Selection

Then select the cells that you want to lookup prior to running the macro.

HTH,
Bernie
MS Excel MVP

 

[davidnagel]

Thank you for your assistance Bernie, your advice worked a treat and the
Macro produces a perfect worksheet. Can we not share karma points here? ;-)

Kind Regards and Best Wishes,

David.