Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
' the macro applies an automatic filtering on the Open invoice and
' unmatched payments sheets retrieving the details of the client selected
' from the drop down in cell C4
'If only one cell is selected
If Target.Cells.Count = 1 Then
'If the selected cell is B4 cell of the Matching Panel
If Target = Sheets("Matching Panel").Range("C4") Then
'Determines the total number of columns in the first list
LastCol = Sheets("Matching Panel").Cells(6, Columns.Count).End(xlToLeft).Column
'Determines the total number of rows in the first list
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Clear the previous filters
Sheets("Matching Panel").Range(Cells(6, 1), Cells(LastRow, LastCol)).Clear
'Only fields Column01 to Column04 and Column06
Sheets("Unmatched payments").Range("A1:D1,F1").Copy _
Destination:=Sheets("Matching Panel").Range("A6:E6")
'Filter the data of the Unmatched payments and copy the filtered records
'to Matching Panel
Sheets("Unmatched payments").Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Matching Panel").Range("A6:E6"), _
CriteriaRange:=Sheets("Unmatched payments").Range("AB1:AB2")
'Determines the total number of columns in the first list
LastCol = Sheets("Matching Panel").Cells(6, Columns.Count).End(xlToLeft).Column
'Only fields Column01,Column02 and Column08
Sheets("Open Invoices").Range("A1:B1,H1").Copy _
Destination:=Sheets("Matching Panel").Range(Cells(6, LastCol + 2), Cells(6, LastCol + 4))
'Filter the data of the open invoices and copy the filtered records
'to Matching Panel
Sheets("Open Invoices").Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Matching Panel"). _
Range(Cells(6, LastCol + 2), Cells(6, LastCol + 4)), _
CriteriaRange:=Sheets("Open Invoices").Range("AB1:AB2")
End If
End If
Application.ScreenUpdating = True
End Sub
