compare then copy to next page

Posted by Andonny on May 03, 2001 3:17 AM

Hi,
I have generally the same items listed on sheet1 and sheet2. Sometimes I get more in sheet1 and therefore
I am trying to find all the extra items listed on sheet1. Then I need to transfer those to sheet 2 and add them to the bottom of the already existing rows. I have the item numbers in column A and the item names in column B.

How would I be able to solve this problem?

Thank you very much
Andonny

Posted by David Hawley on May 03, 2001 5:09 AM

Hi Andonny

Try this code:

Sub TryThis()
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''''
'Compares Column A of one sheet with Column A of
'another and copies over any non existing entries.
''''''''''''''''''''''''''''''''''''''''''''''

Dim rCell As Range
Dim LookRange As Range
Application.ScreenUpdating = False

Set LookRange = Range("A1", Range("A65536").End(xlUp))
For Each rCell In LookRange

If WorksheetFunction.CountIf _
(Sheets("Sheet2").Columns(1), rCell.Text) = 0 Then
rCell.Range("A1:B1").Copy _
Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

End If
Next rCell

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Dave

OzGrid Business Applications

Posted by Andonny on May 03, 2001 5:37 AM

Hi Dave,
Thanks a million. This code is just fantastic.

Take care
Andonny