Thanks for your quick reply. I ran the code with little changes to column
numbers to represent the correct columns in sheets 1 and 2.

If mySheet2.Cells(i, 7) = mySheet1.Cells(i, 10)

Upon execution, I get a code 9 " Subscript out of range" error.
I wonder why it could be happening.


Thanks again for your input.

nshring
"japfvg" wrote:

Hi,

I had this type of process regularly and what I like to do is like this:

option explicit
sub Process()
'Creating the variables and asign them the sheets you use

dim mySheet1 as worksheet, mySheet2 as worksheet
dim mySheet3 as worksheet, i as long, j as long, k as integer

set mysheet1 = worksheets("Sheet 1")
set mysheet2 = worksheets("Sheet 2")
set mysheet3 = worksheets("Sheet 3")

i = 2
j = 2
'Going through your data and comparing cells
while mysheet2.cells(i,1) < ""

'Compare the cells in the 2 worksheets
if mysheet2.cells(i,1) = mysheet1.cells(i,2) then

'Put the value of every column in sheet1 into sheet3
for k = 1 to 15
mysheet3 .cells(j,k) = mysheet1.cells(i,k)
next
j = j + 1

end if
i = i + 1

wend

If you have any questions, please let me know.
I hope this helps

"nshring" wrote:

Hello,
I have a scenario in which there is one excel 2007 workbook with three
sheets in it.
Sheet 1 is a master sheet with around 1900 rows and 15 columns of data.
Sheet 2 contains the qualifying data.
Sheet 3 is a result (or an output) sheet.

Sheet 2 has a column A with unique 5 digit integers.
The goal is to compare every cell of column A of Sheet 2 with Column B in
Sheet 1.
If there is a match, then copy that whole row of Sheet 1 into Sheet 3.
Repeat until all the cells in Column A of Sheet 2 have been compared with
Column B of Sheet 1.

What is the best way to achieve this?

Thanks much!