Filtering for All Items 1

[EliseFreedman]

Hi There

I have been working on a spreadsheet which contains information about overdue reports from different managers within the business. The information is all contained within a single spreadsheet. I want to be able to email each manager individually with the details of only his/her overdue statements

At the moment I have got it to the stage where I can select a manager from the drop down combo box, click on filter report and it filters it for that manager then emails him. (See Code Below) I would now like to modify it so that at the click of a button, it works its way through all items in the listbox (values of which are taken from a list elsewhere in the spreadsheet: Cells N3:O731 on Menu Sheet In workbook), filters the data for that manager then sends the email then moves on to the next manager in the list. I am not sure how to do this. I had a wee play about with loops but got nowhere!! Can anyone point me in the right direction?

My code is currently as follows

Sub FilterData_Location()
Worksheets("Criteria").Select
'   Local Variables
Dim blnProjIdMatch          As Boolean, blnCustIDMatch          As Boolean
Dim rngProjStatus           As Range
Dim strProjID               As String, strCustID                As String
Dim strProjID2               As String
Dim AreaName As String

'   Step 1 : Retrieve information

Range("c_SelProjID") = Range("G1")
strProjID = Range("c_SelProjID")
strProjID2 = " " & strProjID

'   Step 2 : Clear any existing data
Worksheets("Criteria").Range("7:65536").ClearContents


'   Step 4 : Get relevant data from status page(which contains all data for all managers)and copy to criteria page (contains only filtered data)

For Each rngProjStatus In Range("ps_ProjectNums")
blnProjIdMatch = False
If rngProjStatus = strProjID2 Or strProjID2 = "" Then blnProjIdMatch = True
                
If blnProjIdMatch = True Then
rngProjStatus.EntireRow.Copy Destination:=Worksheets("Criteria").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next rngProjStatus
Call RemoveValidation

'Step 5 : Email attached spreadsheet to appropriate Manager
Call Mail_ActiveSheet
End Sub

 

[Bong]

In the range, sheets("Menu").range("N3:O731"), you have some information that will guide your "filtering". What is the nature of that information? It's 2 columns (728 rows!) so I'm guessing maybe "manager name" and "project number"?

Presumably, from that information you can determine where in the data to look, see if some criteria, are met, pull the appropriate data, and email.

I'd loop though it:

    For Each r In Sheets("Menu").[N3:O731].Rows
        pm_name=r.Cells(1)
        PN=r.Cells(2)
    Next

Or something like that.

_________________
Bob Rashkin

 

[PHV]

728 rows[/!]
729 for me ...

 

[Bong]

Yes, of course.

_________________
Bob Rashkin

 

[EliseFreedman]

Thanks for this.

I tried it yesterday and it works like a dream