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

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

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

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

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

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

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" <Mona@discussions.microsoft.com> wrote in message
news:AB90A163-6B49-4FB9-B14C-66549DEED555@microsoft.com...
> 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 !!