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).R ange("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.

On Apr 22, 5:06*pm, Bradly wrote:
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).R ange("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).R ange("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