copying data using an array with criteria

I have a sheet, for sake of example lets say it looks like this:

Name Patient# Hair Color Eye Color
John 1 Brown Blue
Mary 3 Blonde Green

Lets call the sheet "TodaysPatients".

I have sorted "TodaysPatients" according to patient#.

I have another sheet calls "Patients" with all patient #s from 1 - 50 listed in column A.

Problem: I want to take all the rows from "TodaysPatients" and copy them to "Patients" lining up their Patient Numbers. To make the the problem more complicated, I ONLY want to copy columns 1 and 3 and put them in colukmn 2 and 3 on the "Patients" sheet.

Iw ould like ot do this in an array to avoid multiple changes from VBA to excel and many sheet changes.


I need to do this all in VBA. PLEASE PLEASE PLEASE help!!

Thanks in Advance!

No need to use arrays

Try this macro

Please Login or Register  to view this content.

I am getting an "Invalid use of object" on this line:

Please Login or Register  to view this content.

So I tried

Please Login or Register  to view this content.

and am now getting a subscript out of range on:

Please Login or Register  to view this content.

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The macro is treating the word Sample as a variable.
If Sample is a test word you are using then it needs to be inclosed in "

Please Login or Register  to view this content.