How do I compare two sheets cell values and paste to new workbook if criteria is matched? Will give gold

So I deleted my other comment because I re-read and realized I'm an idiot. First off, I saw you wanted in a new workbook, and I realized that this would have to be done outside your other If statement. Anyway, I think this is what you'd want. I commented out what I added so that you could clearly see what I'm doing. I don't think it's that crazy.

'Dim wbNew As Workbook, wsNew As Worksheet, lrwsNew As Long
'Set wbNew = Workbooks.Add
'Set wsNew = wbNew.Sheets("Sheet1")
'lrwsNew = 1

Set new_col_report = Sheets(1).Range("A2:A" & new_lastrow).SpecialCells(xlCellTypeVisible)
Set old_col_report = old_rep_sh.Range("A2:A" & old_lastrow).SpecialCells(xlCellTypeVisible)
ct = 2
ct1 = 2
ct2 = 2
'loop searching and matching
For Each cel In new_col_report
    If Not IsEmpty(cel.Value) Then
    'look up if matching
        Set search_cel = old_col_report.Find(cel.Value, LookIn:=xlValues)
        'match found
        If Not search_cel Is Nothing Then
        'copy to new rows

            ct = cel.Row
            ct1 = search_cel.Row

            If _
            new_rep_sheet.Cells(ct, "F") <> old_rep_sh.Cells(ct1, "F") Or _
            new_rep_sheet.Cells(ct, "AC") <> old_rep_sh.Cells(ct1, "AC") Or _
            new_rep_sheet.Cells(ct, "AD") <> old_rep_sh.Cells(ct1, "AD") Or _
            new_rep_sheet.Cells(ct, "AE") <> old_rep_sh.Cells(ct1, "AE") Or _
            new_rep_sheet.Cells(ct, "AF") <> old_rep_sh.Cells(ct1, "AF") Or _
            new_rep_sheet.Cells(ct, "AG") <> old_rep_sh.Cells(ct1, "AG") Or _
            new_rep_sheet.Cells(ct, "AI") <> old_rep_sh.Cells(ct1, "AI") Or _
            new_rep_sheet.Cells(ct, "AJ") <> old_rep_sh.Cells(ct1, "AJ") Or _
            new_rep_sheet.Cells(ct, "AK") <> old_rep_sh.Cells(ct1, "AK") _
            Then
                search_cel.EntireRow.Copy change_val_sh.Rows(ct2)
                cel.EntireRow.Copy change_val_sh.Rows(ct2 + 1)
                ct2 = ct2 + 2
            End If

           ' If _
           ' new_rep_sheet.Cells(ct, "AC") = "TPHS" And old_rep_sh.Cells(ct, "AC") = "TPHS" And old_rep_sh.Cells(ct1, "AD") = "No" And new_rep_sheet.Cells(ct, "AD") = "No" _
           ' Then

            '    search_cel.EntireRow.Copy
            '   wsNew.Cells(lrwsNew, "A").Paste
            '    cel.EntireRow.Copy
            '    wsNew.Cells(lrwsNew + 1, "A").Paste

            '    lrwsNew = lrwsNew + 2


           ' End If


        Else

            cel.EntireRow.Copy new_rows_sh.Rows(ct)
                ct = ct + 1
        End If

    End If

    search_cel = Null
Next cel
/r/excel Thread