Prev: date data type is not a date??
Next: disable pageup and pagedown
From: Bradly on 22 Apr 2010 17:06
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.

From: Jef Gorbach on 22 Apr 2010 20:40
On Apr 22, 5:06 pm, Bradly <Bra...(a)discussions.microsoft.com> 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).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
 | 
Pages: 1
Prev: date data type is not a date??
Next: disable pageup and pagedown