Macro to Search and Copy
 

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?

Macro to Search and Copy
 

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" wrote in message
...
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?

Macro to Search and Copy
 

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" wrote:

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" wrote in message
...
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?

Macro to Search and Copy
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

Macro to Search and Copy
 

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.