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.
Click to expand...

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

Similar Threads

input numbers for code 3
help with dates in the past 5
filtering two pieces of data 3
stopping a macro 0
cannot insert row in vba 1
another filtering question 1
Userform to increment line item numbers with textboxes-spinbuttons 5
creating a loop 1

Top