help with match, copy, paste please
 

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 !!

help with match, copy, paste please
 

Why not just use VLookup worksheet function?
--
HTH...

Jim Thomlinson


"Mona" wrote:

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 !!

help with match, copy, paste please
 

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

--
Regards,
Tom Ogilvy


"Mona" wrote:

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 !!

help with match, copy, paste please
 

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

"Jim Thomlinson" wrote:

Why not just use VLookup worksheet function?
--
HTH...

Jim Thomlinson


"Mona" wrote:

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 !!

help with match, copy, paste please
 

Tom-
this is perfect! thank you

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Mona" wrote:

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 !!

help with match, copy, paste please
 

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

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

--
Regards,
Tom Ogilvy


"Mona" wrote:

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 !!

help with match, copy, paste please
 

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

--
regards,
Tom Ogilvy

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

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

--
Regards,
Tom Ogilvy


"Mona" wrote:

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 !!