mtach - copy entire row from 1 sheet to another

[oddworld]

Hi all, i have trolled through a number of posts, i can't find an a match for what i am looking for.

I have 1 workbook with 2 sheets, I would like some advice on a macro to copy rows of data from sheet 1 that *are not listed in sheet2, and copy them to sheet 3. Unique values are in column "bn" on each sheet.

any assistance would be appreciated, need to sort this out

 

[vcoolio]

Hello Oddworld,

Here's an option:-

Sub Test()

         Dim fValue As Range
         Dim c As Range
         Dim lr As Long: lr = Sheet1.Range("BN" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

         Sheet3.UsedRange.ClearContents
         Sheet1.UsedRange.Copy Sheet1.[HA1]

For Each c In Sheet1.Range("BN2:BN" & lr)
         Set fValue = Sheet2.Columns("BN:BN").Find(c.Value)
         If fValue Is Nothing Then GoTo Nextval
         If c.Value = fValue.Value Then
         Range(c, c.Offset(, -65)).ClearContents
    End If
Nextval:
         If c.Value <> "" Then
         Range(c, c.Offset(, -65)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
    End If
Next c

        Sheet1.[HA1].CurrentRegion.Copy Sheet1.[A1]
        Sheet1.[HA1].CurrentRegion.Clear
        Sheet3.Select

Application.ScreenUpdating = True

End Sub

I'm assuming that your data columns are from A:BN with all three sheets having headings in Row1.
The code also temporarily uses helper columns from HA and beyond.

I hope that this helps.

Cheerio,
vcoolio.

P.S. I'm also assuming that all data will remain in sheets 1 and 2.