Only copy rows based on cell value and Paste (values only) into a new table. BankReconciliation Project.

[Spotycus]

I have seen this topic a lot recently but have not been able to apply the prior examples correctly for my scenario. I am working on a bank reconciliation tool, and need generating the table of mismatched or unreconciled values. Hopefully the following information will help explain.

I have two tables of information QBOTransactions and BankTranasactions that have share the same formatting including headers. The Unique Column ensures that there are no two transactions within the same table that identical. The letter helps to signify the type of transaction as well. The Matched column uses the following formula to compare the unique codes between the two tables to reconcile the transactions.

=IF(COUNTIF($BP$195:$BP$316,BD195)=1,"R","")

Based on the value in the Matched Columns I would like to copy the transactions that DO NOT have an R to new tables. QBOTransactions would be copied to QBOUncleared and BankTransactions would be copied to BankUncleared . Below is an example of the desired outcome

I know it can be difficult to obtain help when only providing pictures so I will provide the other vital information below. Although I am still progressing my knowledge of VBA, I do feel rather comfortable changing values to match my specific range or cell locations. I just do not know how to best transfer the information from one table to the other so that it is organized by the date and then highlighting the errors. If it helps with the conditional formatting, the Unique value will point out the problem area (94.75 = transaction amount -E= transaction type)

Sheets= Sheet1 (Bank Reconciliation)
QBOTransactions Table= ($AX$194:$BE311)
BankTransactions Table= ($BJ$194:$BQ311)
QBOUncleared Table= ($C$9:$J30)
BankUncleared Table= ($M$9:$T30)

Thanks in advance for any advice or help!

 

[JLGWhiz]

There might be a faster way to do it, but this seems to work.

Sub unreconcld()
Dim sh As Worksheet, c As Range
Set sh = Sheets("Sheet1")
    With sh
    For Each c In .Range("BE195:BE311")
        If c <> "R" Then
            c.Offset(, -6).Resize(1, 4).Copy .Cells(Rows.Count, 3).End(xlUp)(2)
            If c.Offset(, -5) = "Check" Then
                .Cells(Rows.Count, 3).End(xlUp)(1, 5) = c.Offset(, -2).Value
            ElseIf c.Offset(, -5) = "Expense" Then
                .Cells(Rows.Count, 3).End(xlUp)(1, 6) = c.Offset(, -2).Value
            ElseIf c.Offset(, -5) = "Deposit" Then
                .Cells(Rows.Count, 3).End(xlUp)(1, 7) = c.Offset(, -2).Value
            End If
            .Cells(Rows.Count, 3).End(xlUp)(1, 8) = c.Offset(, -1).Value
        End If
    Next
    For Each c In .Range("BQ195:BQ311")
        If c <> "R" Then
            c.Offset(, -6).Resize(1, 4).Copy .Cells(Rows.Count, 13).End(xlUp)(2)
            If c.Offset(, -5) = "Check" Then
                .Cells(Rows.Count, 13).End(xlUp)(1, 5) = c.Offset(, -2).Value
            ElseIf c.Offset(, -5) = "Expense" Then
                .Cells(Rows.Count, 13).End(xlUp)(1, 6) = c.Offset(, -2).Value
            ElseIf c.Offset(, -5) = "Deposit" Then
                .Cells(Rows.Count, 13).End(xlUp)(1, 7) = c.Offset(, -2).Value
            End If
            .Cells(Rows.Count, 13).End(xlUp)(1, 8) = c.Offset(, -1).Value
        End If
    Next
    End With
End Sub