ExcelBanter - Macro to copy/paste

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro to copy/paste (https://www.excelbanter.com/excel-programming/404696-macro-copy-paste.html)

Macro to copy/paste

In worksheet A,I have a range of data beginnning in a2;
A B C D
1 Unit# Client Name Phone# Address
2 119 Fred Wilson 6561578 34 anystreet

In worksheet B, a have a list of data organized the same way;
A B C D
124 119 Fred Wilson 6561578 34 anystreet
125 120 John Smith 6564877 39 Anytown

I need a macro to find the cell containing the unit number from Worksheet A
in column A on Worksheet B, and then select it. Then copy/paste values the
data on Worksheet A (A2:D2 in my example) over to Worksheet B (would be A124
matching unit number 119 in my example).

Macro to copy/paste

I would not solve this problem with a macro, just with a vlookup. Create a
Vlookup formula for each column of data you want to show up in worksheet B
from worksheet A. Use the first column A as the item that is being looked up
between worksheets since it is already in both worksheets. Once the Vlookup
formulas work correctly in each column use the "$" feature to freeze the
range consistantly and freeze the column that is being looked up. Then copy
the vlookup formulas down the document to complete.

"Lonpuz" wrote:

In worksheet A,I have a range of data beginnning in a2;
A B C D
1 Unit# Client Name Phone# Address
2 119 Fred Wilson 6561578 34 anystreet

In worksheet B, a have a list of data organized the same way;
A B C D
124 119 Fred Wilson 6561578 34 anystreet
125 120 John Smith 6564877 39 Anytown

I need a macro to find the cell containing the unit number from Worksheet A
in column A on Worksheet B, and then select it. Then copy/paste values the
data on Worksheet A (A2:D2 in my example) over to Worksheet B (would be A124
matching unit number 119 in my example).

Macro to copy/paste

James

I forgot to mention that the value (unit #) in Worksheet A A2 is variable.
I have come up with a "long way" to possibly solve the issue using a VLOOKUP
nested in an IF statement in every cell (huge range). Doing this I could use
a macro to populate the few thousand cells with a formula that if true
(correct corresponding unit#)would copy/paste the target cells, and if untrue
replace the cell contents with values from a reference worksheet. I would of
course have to generate a reference worksheet, and then update it at the end
of the macro. Might take a long time for that macro to run though. What do
you think?

"JamesT" wrote:

I would not solve this problem with a macro, just with a vlookup. Create a
Vlookup formula for each column of data you want to show up in worksheet B
from worksheet A. Use the first column A as the item that is being looked up
between worksheets since it is already in both worksheets. Once the Vlookup
formulas work correctly in each column use the "$" feature to freeze the
range consistantly and freeze the column that is being looked up. Then copy
the vlookup formulas down the document to complete.

"Lonpuz" wrote:

In worksheet A,I have a range of data beginnning in a2;
A B C D
1 Unit# Client Name Phone# Address
2 119 Fred Wilson 6561578 34 anystreet

In worksheet B, a have a list of data organized the same way;
A B C D
124 119 Fred Wilson 6561578 34 anystreet
125 120 John Smith 6564877 39 Anytown

I need a macro to find the cell containing the unit number from Worksheet A
in column A on Worksheet B, and then select it. Then copy/paste values the
data on Worksheet A (A2:D2 in my example) over to Worksheet B (would be A124
matching unit number 119 in my example).

Macro to copy/paste

Sorry Don, but I'm not sure what you mean by "active cell". Is that the cell
in the range on Worksheet B that contains the matching number?

"Don Guillett" wrote:

This will find 119 if in the activecell of the destination sheet on the
source sheet and copy the entire row to the row of the active cell.

Sub findandcopy()
what = ActiveCell
Sheets("sheet2").Rows(Sheets("sheet2").Columns(1) _
.Find(what).Row).Copy ActiveCell
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lonpuz" wrote in message
...
In worksheet A,I have a range of data beginnning in a2;
A B C D
1 Unit# Client Name Phone# Address
2 119 Fred Wilson 6561578 34 anystreet

In worksheet B, a have a list of data organized the same way;
A B C D
124 119 Fred Wilson 6561578 34 anystreet
125 120 John Smith 6564877 39 Anytown

I need a macro to find the cell containing the unit number from Worksheet
A
in column A on Worksheet B, and then select it. Then copy/paste values
the
data on Worksheet A (A2:D2 in my example) over to Worksheet B (would be
A124
matching unit number 119 in my example).

Macro to copy/paste