copy rows that do not match values in dynamic range.
 

Hi every one,

Please help vba/excel newbie!

I have 3 sheets with columns SKU and Quantity.
Sheet1 is raw data. Sheet2 is in house stock and Sheet3 is outside stock.

What I need is to loop through the rows in sheet1 and check if the SKU
exists in Sheet2 if not copy the row into Sheet3.

The problem is that the SKUs in Sheet2 (the one I'm matching against) change
all the time so I can't hard code them.

Thanks in advance for any advice!

copy rows that do not match values in dynamic range.
 

Sub copyrows()
Dim rng1 as Range, cell as Range
Dim sh1 as worksheet, sh2 as worksheet
dim sh3 as worksheet, rw as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set sh3 = worksheets("sheet3")
rw = sh3.cells(rows.count,1).End(xlup).row + 1
set rng1 = sh1.range(sh1.cells(2,1),sh1.cells(rows.count,1).E nd(xlup))
for each cell in rng1
if application.countif(sh2.columns(1),cell) = 0 then
cell.entireRow.copy sh3.rows(rw)
rw = rw + 1
end if
Next
End sub
--
Regards,
Tom Ogilvy


"Zebrahead" wrote:

Hi every one,

Please help vba/excel newbie!

I have 3 sheets with columns SKU and Quantity.
Sheet1 is raw data. Sheet2 is in house stock and Sheet3 is outside stock.

What I need is to loop through the rows in sheet1 and check if the SKU
exists in Sheet2 if not copy the row into Sheet3.

The problem is that the SKUs in Sheet2 (the one I'm matching against) change
all the time so I can't hard code them.

Thanks in advance for any advice!

copy rows that do not match values in dynamic range.
 

That works great. Thank you very much!!!

"Tom Ogilvy" wrote:

Sub copyrows()
Dim rng1 as Range, cell as Range
Dim sh1 as worksheet, sh2 as worksheet
dim sh3 as worksheet, rw as Long
set sh1 = worksheets("Sheet1")
set sh2 = worksheets("Sheet2")
set sh3 = worksheets("sheet3")
rw = sh3.cells(rows.count,1).End(xlup).row + 1
set rng1 = sh1.range(sh1.cells(2,1),sh1.cells(rows.count,1).E nd(xlup))
for each cell in rng1
if application.countif(sh2.columns(1),cell) = 0 then
cell.entireRow.copy sh3.rows(rw)
rw = rw + 1
end if
Next
End sub
--
Regards,
Tom Ogilvy


"Zebrahead" wrote:

Hi every one,

Please help vba/excel newbie!

I have 3 sheets with columns SKU and Quantity.
Sheet1 is raw data. Sheet2 is in house stock and Sheet3 is outside stock.

What I need is to loop through the rows in sheet1 and check if the SKU
exists in Sheet2 if not copy the row into Sheet3.

The problem is that the SKUs in Sheet2 (the one I'm matching against) change
all the time so I can't hard code them.

Thanks in advance for any advice!