Quote Originally Posted by hecgroups View Post
If the entire criteria match within the specified dates then copy specific columns (Excluding Column F, I, J, K & R) from worksheets data and past to worksheets statement. After copy the data then look for the below match.

From worksheets list look for the provider name into worksheets Resubmission Adjustment into column F and look for the batch no in column B which is present in worksheets “Data” in column G and worksheets “Resubmission Adjustment” in column D cell value is Resubmission Adjustment the copy those rows only.
If this is not how you wanted, need to see your exact desired result.
Sub test()
    Dim a, e, i As Long, LastR As Range, flg As Boolean
    Dim cn As Object, rs As Object
    Dim rng As Range, x
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;MEX=1;"
        .Open ThisWorkbook.FullName
    End With
    rs.Open "Select `Date`, `Date`, Format(`Settlement To`,'yyyy/m/d'), Null, `Settlement For`, " & _
            "`Batch No`, 'Resub', Null, Null, Null, `Amount` From `Resubmission Adjustment$`", cn, 3
    Set rng = Sheets("Resubmission Adjustment").Cells(1).CurrentRegion
    Sheets("Statement").Cells.ClearContents
    a = Sheets("list").Cells(1).CurrentRegion.Value
    With Sheets("data").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For i = 2 To UBound(a, 1)
            .AutoFilter 4, a(i, 1)
            .AutoFilter 6, a(i, 3)
            .AutoFilter 3, ">=" & a(i, 4), 1, "<=" & a(i, 5)
            If .Parent.Evaluate("subtotal(3," & .Columns(1).Address & ")") > 1 Then
                With .Offset(IIf(flg, 1, 0))
                    Set LastR = IIf(flg, Sheets("Statement").Range("a" & Rows.Count).End(xlUp)(2), Sheets("Statement").[a1])
                    Union(.Columns("a:e"), .Columns("g:h"), .Columns("l:q")).Copy LastR
                End With
                x = Filter(rng.Parent.Evaluate("transpose(if(" & rng.Columns("f").Address & _
                    "=""" & a(i, 2) & """," & rng.Columns("b").Address & "))"), False, 0)
                If UBound(x) > -1 Then
                    For Each e In x
                        rs.Filter = "[Batch No] = '" & e & "'"
                        Sheets("statement").Range("a" & Rows.Count).End(xlUp)(2).CopyFromRecordset rs
                    Next
                End If
                flg = True
            End If
            .AutoFilter
        Next
    End With
End Sub