VBA - running filtered reports based on pre-set criteria

jflaherty

New Member
Joined
Sep 14, 2018
Messages
3
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:

VBA Code:
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:
Screenshot 2021-07-30 at 11.50.33.png


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
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,147,482
Messages
5,741,406
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top