excel - Compare sheets to copy differences -

i have working vba code compares 2 sheets , copies duplicates 'changes' sheet. need opposite. need copy differences changes sheet.

sub comparesheets() dim sht1rng range dim sht2rng range  set sht1rng = worksheets("rxcp order").range("a1", worksheets("rxcp order").range("a1000").end(xlup)) set sht2rng = worksheets("qs1 order").range("a1", worksheets("qs1 order").range("a1000").end(xlup))  each c in sht1rng     set d = sht2rng.find(c.value, lookin:=xlvalues)     if not d nothing         worksheets("changes").range("a1000").end(xlup).offset(1, 0).value = c.value         worksheets("changes").range("a1000").end(xlup).offset(0, 1).value = c.offset(0, 1).value         set d = nothing     end if next c end sub 

one way find changes 1 worksheet add sheet3 following formula in each cell of used range of sheet1:

=if(sheet1!a1<>sheet2!a1,1,0) 

then add column sheet3 adding across row. rows count greater 0 should copied. can filter copy paste manually or run loop down count column in sheet 3 , copy row sheet 2 based on row on in sheet 3

dim acell range thisworkbook each acell in .sheets("sheet3").range("z1:z1000")   if acell.value > 0     .sheets("sheet1").range("a" & acell.row).entirerow.copy _     .sheets("sheet4").range("a" & .sheets("sheet4").usedrange.rows.count + 1)   end if next acell end 

you can write macro copy formula above cells in sheet3's used range, , copy formula down next available column also.