setting more than one filter

B

Bradly

I have the following code that asks for a caseload ID#, filters the
designated sheet for that caseload ID#, and pastes all cases for that load in
a destination sheet (it also puts a formula at the top to count the total
number of cases):


Sub SortReviews()
'
' SortReviews Macro
'

'
Windows("Reviews Distribute.xls").Activate
myCaseload = Application.InputBox("Enter a caseload ID#.")


Sheets(myCaseload).Activate
Application.Goto Reference:="R1C1"


Dim FilterRange As Range
Dim CopyRange As Range
Dim MasterWbk As Workbook
Dim TargetWbk As Workbook
Set MasterWbk = Workbooks("SeparatedCases2010.xls")
With MasterWbk.Worksheets("F Only Cases")
Set FilterRange = .Range("H1:H3000") 'Header in row
Set CopyRange = .Range("A1:M3000")
End With

FilterRange.AutoFilter Field:=1, Criteria1:=myCaseload
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ThisWorkbook.Worksheets(myCaseload).Range("A3")
Application.CutCopyMode = False
Windows("SeparatedCases2010.xls").Activate
Sheets("F Only Cases").Activate
Selection.AutoFilter
Application.Goto Reference:="R1C1"
Windows("Reviews Distribute.xls").Activate
Sheets(myCaseload).Activate
Application.Goto Reference:="R1C1"


ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate
Selection.EntireRow.Delete
Application.Goto Reference:="R1C1"
ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C[-3]:R[9998]C[-3])"
Selection.Offset(0, -1).Range("A1:B1").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Application.Goto Reference:="R1C1"
End Sub


Is it possible to adapt this to filter out a month within the total list of
cases? What this does now is to filter all cases that belong to each case
manager--lets say CM1. What I would like for this to do is to filter for
each case manager, then filter for a given month for each case manager. For
example, filter for CM1 and then filter for June (I could add an input box to
ask for the specific month, I guess).

Please let me know if you need additional information for this request.
Thanks.
 
J

Jef Gorbach

I have the following code that asks for a caseload ID#, filters the
designated sheet for that caseload ID#, and pastes all cases for that load in
a destination sheet (it also puts a formula at the top to count the total
number of cases):

Sub SortReviews()
'
' SortReviews Macro
'

'
    Windows("Reviews Distribute.xls").Activate
    myCaseload = Application.InputBox("Enter a caseload ID#.")

    Sheets(myCaseload).Activate
    Application.Goto Reference:="R1C1"

    Dim FilterRange As Range
    Dim CopyRange As Range
    Dim MasterWbk As Workbook
    Dim TargetWbk As Workbook
    Set MasterWbk = Workbooks("SeparatedCases2010.xls")
    With MasterWbk.Worksheets("F Only Cases")
        Set FilterRange = .Range("H1:H3000") 'Header in row
        Set CopyRange = .Range("A1:M3000")
    End With

    FilterRange.AutoFilter Field:=1, Criteria1:=myCaseload
    CopyRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=ThisWorkbook.Worksheets(myCaseload).Range("A3")
    Application.CutCopyMode = False
    Windows("SeparatedCases2010.xls").Activate
    Sheets("F Only Cases").Activate
    Selection.AutoFilter
    Application.Goto Reference:="R1C1"
    Windows("Reviews Distribute.xls").Activate
    Sheets(myCaseload).Activate
    Application.Goto Reference:="R1C1"

    ActiveCell.Offset(rowOffset:=2, columnOffset:=0).Activate
    Selection.EntireRow.Delete
    Application.Goto Reference:="R1C1"
    ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "Total"
    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
    Selection.Font.Bold = True
    ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C[-3]:R[9998]C[-3])"
    Selection.Offset(0, -1).Range("A1:B1").Select
    With Selection.Interior
        .ColorIndex = 37
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Application.Goto Reference:="R1C1"
End Sub

Is it possible to adapt this to filter out a month within the total list of
cases?  What this does now is to filter all cases that belong to each case
manager--lets say CM1.  What I would like for this to do is to filter for
each case manager, then filter for a given month for each case manager.  For
example, filter for CM1 and then filter for June (I could add an input box to
ask for the specific month, I guess).

Please let me know if you need additional information for this request.
Thanks.

Untested, but pretty sure all you need to add is another inputbox for
the month then add a second filter line after your first for it, where
# is the corresponding column number (ie: column(d) = 4)

Sub SortReviews()
' SortReviews Macro
Windows("Reviews Distribute.xls").Activate
myCaseload = Application.InputBox("Enter a caseload ID#.")
myMonth = Application.InputBox("Enter desired month")
Sheets(myCaseload).Activate
Application.Goto Reference:="R1C1"
Dim FilterRange As Range
Dim CopyRange As Range
Dim MasterWbk As Workbook
Dim TargetWbk As Workbook
Set MasterWbk = Workbooks("SeparatedCases2010.xls")
With MasterWbk.Worksheets("F Only Cases")
Set FilterRange = .Range("H1:H3000") 'Header in row
Set CopyRange = .Range("A1:M3000")
End With
FilterRange.AutoFilter Field:=1, Criteria1:=myCaseload
FilterRange.AutoFilter Field:=#, Criteria2:=myMonth
CopyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ThisWorkbook.Worksheets(myCaseload).Range("A3")
Application.CutCopyMode = False
Windows("SeparatedCases2010.xls").Sheets("F Only Cases").Activate
Selection.AutoFilter
Windows("Reviews Distribute.xls").Sheets(myCaseload).Activate
ActiveCell.Offset(rowOffset:=2, columnOffset:=0).EntireRow.Delete
ActiveCell.Offset(rowOffset:=0, columnOffset:=10).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=COUNTA(R[2]C[-3]:R[9998]C[-3])"
Selection.Offset(0, -1).Range("A1:B1").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Application.Goto Reference:="R1C1"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top