help with match, copy, paste please

[Guest]

I have example data on worksheet ("results"):

A B C D
1 maybe car
8 maybe van 03/04/2007

I will randomly change any or all of the data ONLY in columns B,C, or D to
something like this:

A B C D
1 maybe car 09/12/2006
8 no van 05/26/2007

I would like help in writting the code to COPY the entire row/rows from
"results" and paste to a "master" worksheet when column A ("results") matches
column A ("master"). Column A in both "master" and "results" are unique
values.

thank you !!

 

[Guest]

Why not just use VLookup worksheet function?

 

[Guest]

I could. But I am putting buttons on the worksheet so user has the ability
to change master with the click of the button

 

[Guest]

Sub CopyResults()
Dim res as Variant
Dim resRng as Range
Dim masRng as Range
Dim rng as Range
With worksheets("Results")
set resRng = .Range(.Cells(1,1),.cells(1,1).End(xldown))

With worksheets("Master")
set masRng = .Range(.Cells(1,1),.cells(1,1).End(xldown))
end with
for each cell in resRng
res = application.Match(cell.value, masRng,0)
if not iserror(res) then
cell.Entirerow.copy masRng(res)
else
set rng = .cells(rows.count,1).end(xlup)(2)
cell.entirerow.copy rng
end if
Next
End With
End Sub

Test on a copy of your workbook

 

[Guest]

Tom-
this is perfect! thank you

Sub CopyResults()
Dim res as Variant
Dim resRng as Range
Dim masRng as Range
Dim rng as Range
With worksheets("Results")
set resRng = .Range(.Cells(1,1),.cells(1,1).End(xldown))

With worksheets("Master")
set masRng = .Range(.Cells(1,1),.cells(1,1).End(xldown))
end with
for each cell in resRng
res = application.Match(cell.value, masRng,0)
if not iserror(res) then
cell.Entirerow.copy masRng(res)
else
set rng = .cells(rows.count,1).end(xlup)(2)
cell.entirerow.copy rng
end if
Next
End With
End Sub

Test on a copy of your workbook

 

[Guest]

Tom-
this just came up and I can't seem to figure out. Using the "perfect" code
below work when I have 2 or more rows of data. But if I only have 1 row of
data I get an error but it actually updates my "master". From time to time I
will have only one row of data. Can you help me with this one? Thanks!

 

[Tom Ogilvy]

My guess would be to change
set resRng = .Range(.Cells(1,1),.cells(1,1).End(xldown))

to

if .cells(1,1).End(xldown).row <> rows.count then
set resRng = .Range(.Cells(1,1),.cells(1,1).End(xldown))
else
set resRng = .Range("A1")
End if