VBA - running filtered reports based on pre-set criteria

[jflaherty]

Hi all,

I currently am operating a contact database, which ranks contacts in the database A, B or C. Depending on their ranking, they should be contacted within a certain frequency, i.e. As are contacted more frequently than Bs and so on. This is done via an IF statement and kicks out a result.

I currently have a macro running for each of the rankings, that spits out the overdue names onto a reporting tab. This is as follows:

Sub GetOverdueBrokersA()
   Dim Category As Variant, OverdueDays As Variant
   Dim i As Long, j As Long, k As Long
   
   Category = Array("A")
   OverdueDays = Array(31, 60, 61, 90, 91, 1000)
   
   Application.ScreenUpdating = False
   With Sheets("Brokers")
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = 0 To UBound(Category)
         For j = 0 To UBound(OverdueDays) Step 2
            k = k + 1
            .Range("N2:Q2").AutoFilter 2, Category(i)
            .Range("N2:Q2").AutoFilter 3, ">=" & OverdueDays(j), xlAnd, "<=" & OverdueDays(j + 1)
            .AutoFilter.Range.Columns(1).Copy Sheets("Report - Brokers").Cells(11, k)
         Next j
         k = k + 1
      Next i
      .AutoFilterMode = False
   End With
End Sub

This macro is repeated for the B and C names with different degrees of 'overdueness'.

The .Range above (N2:Q2) pulls their name, ranking and number of days overdue, and populates this report:

Currently, this all works perfectly.

However, what I would now like to do is to have three separate reporting tabs for each of the contact owners. Let's call them M, S and J. So I have three new tabs set up - Reporting - M, Reporting - S and Reporting - J set up. So I would like only those contacts which are owned by M to be populated in the Reporting - M tab, with the A, B and C ranking outlined above, and so on for S and J.

In the 'Brokers' tab, from which the macros are pulling the data, there is also an owner column, but I'm stuck in terms of how to amend the three macros to only populate each persons report with their contacts.

As always, any help would be greatly appreciated!

Thanks all