vba to check if matches column A & B before pasting

[cizzett]

I have this code which works perfect to export "New" records to my running data workbook but I wanted to add a check that if the record key in column A already exist AND if column B is a match for status as well then do not add that row of data

Sub UpdateRun()    Application.ScreenUpdating = False
             Call Dec
             Call UnhideAll
    If Ws.Range("E3") > 0 Then
    
            Set Wbk = Workbooks.Open(Fl.Range("B9").Value)
            Set Nws = Wbk.Sheets("Data")
            Set RDPiv = Wbk.Sheets("Pivot")
'---------------------Add records marked as New before matching-------------------------
     With TT.DataBodyRange
        .AutoFilter 2, "New"
          If TT.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
'---------------Copy and Paste Data to file---------------------------------------
        TT.DataBodyRange.SpecialCells(xlVisible).Copy
    Nws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Wbk.RefreshAll
        TT.AutoFilter.ShowAllData
        Wb.RefreshAll
        End If
        End With
End Sub

 

[Marcelo Branco]

Just an idea.
I think you should create a helper column with a formula to check if:
New
NOT(A already exists and B matches Status)
Then use this column to filter the data.

M.

 

[cizzett]

The range already exists, it is autofiltering to "New" Data based on the column that is marked as "New" column B.

This is the same on both but if the workbook "ExporttoDB" is run muliple times before changing from "New" to another status then I will get multiple duplicates of this record added to my master file.

I prefer not to only search by the column A key becuase I could theoretically get occasional duplicated but once the record has been updated it would no longer reflect "New" which would tell me I have a new instance of this.

Thats why I wanted to match column A which is my key and column B which would be the status column.

Just to prevent duplicating a second record for the same record.

 

[Marcelo Branco]

I guess I did not make myself clear. I did not suggest that you search only for Column A.

I thought that instead of filtering the data only by using the "New" criteria, a criteria column was created. Like this
= --AND(C2="New",COUNTIFS(Sheet2!A:A,A2,Sheet2! StatusColumn, B2)=0)

This formulas returns 1 for those rows that satisfy all criteria; Zero, otherwise. Then you can filter by this column Criteria:=1

Just an idea ... I do not know if viable in your real case.

M.