Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filtering two pieces of data
I have the following code which filters the cases for a particular case
manager from an office report and pastes the data on a separate sheet for the case manager (case manager 141V for this example). Sub Get141V() ' ' Get141V Macro ' ' Sheets("Office").Activate Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("B1:B1000") 'Header in row Set CopyRange = Range("A1:M1000") FilterRange.AutoFilter Field:=1, Criteria1:="141V" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("141V").Range("A3") Application.CutCopyMode = False Sheets("Office").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("141V").Activate Application.Goto Reference:="R1C1" End Sub The cases either start with an A or a B. With this code, all A and B cases are pulled and listed together. Is there any way to modify this code to where it would filter for the case manager number AND all B cases only? What I would like to do is this: instead of listing all A and B cases together, I want to just list the B cases. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
filtering two pieces of data
Post sample data and let us know which column do have the cases (starting
with A/B).. -- Jacob (MVP - Excel) "Bradly" wrote: I have the following code which filters the cases for a particular case manager from an office report and pastes the data on a separate sheet for the case manager (case manager 141V for this example). Sub Get141V() ' ' Get141V Macro ' ' Sheets("Office").Activate Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("B1:B1000") 'Header in row Set CopyRange = Range("A1:M1000") FilterRange.AutoFilter Field:=1, Criteria1:="141V" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("141V").Range("A3") Application.CutCopyMode = False Sheets("Office").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("141V").Activate Application.Goto Reference:="R1C1" End Sub The cases either start with an A or a B. With this code, all A and B cases are pulled and listed together. Is there any way to modify this code to where it would filter for the case manager number AND all B cases only? What I would like to do is this: instead of listing all A and B cases together, I want to just list the B cases. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
filtering two pieces of data
If it is ColA; try the below...
Sub Macro() Application.ScreenUpdating = False With Sheets("Office") .Activate .Range("A1").Select .Range("A1:B1000").AutoFilter Field:=1, Criteria1:="=A*" .Range("A1:B1000").AutoFilter Field:=2, Criteria1:="141V" .Range("A1:M" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells( _ xlCellTypeVisible).Copy Destination:=Worksheets("141V").Range("A3") .AutoFilterMode = False End With Sheets("141V").Activate Application.Goto Reference:="R1C1" Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Post sample data and let us know which column do have the cases (starting with A/B).. -- Jacob (MVP - Excel) "Bradly" wrote: I have the following code which filters the cases for a particular case manager from an office report and pastes the data on a separate sheet for the case manager (case manager 141V for this example). Sub Get141V() ' ' Get141V Macro ' ' Sheets("Office").Activate Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("B1:B1000") 'Header in row Set CopyRange = Range("A1:M1000") FilterRange.AutoFilter Field:=1, Criteria1:="141V" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("141V").Range("A3") Application.CutCopyMode = False Sheets("Office").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("141V").Activate Application.Goto Reference:="R1C1" End Sub The cases either start with an A or a B. With this code, all A and B cases are pulled and listed together. Is there any way to modify this code to where it would filter for the case manager number AND all B cases only? What I would like to do is this: instead of listing all A and B cases together, I want to just list the B cases. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
filtering two pieces of data
This worked great. Thanks.
Another question: with the code you showed me, is it possible to modify the code to where more than one case manager ID# could be filtered to get data? In column B of my report are the case manager ID#s, and all case managers in the office are listed. Is it possible to modify the code to where 3 or 4 case managers can be filtered out? Could all cases, for example, for case managers 135, 136, and 137 be filtered and pasted on a separate sheet? Thanks. "Jacob Skaria" wrote: If it is ColA; try the below... Sub Macro() Application.ScreenUpdating = False With Sheets("Office") .Activate .Range("A1").Select .Range("A1:B1000").AutoFilter Field:=1, Criteria1:="=A*" .Range("A1:B1000").AutoFilter Field:=2, Criteria1:="141V" .Range("A1:M" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells( _ xlCellTypeVisible).Copy Destination:=Worksheets("141V").Range("A3") .AutoFilterMode = False End With Sheets("141V").Activate Application.Goto Reference:="R1C1" Application.ScreenUpdating = True End Sub -- Jacob (MVP - Excel) "Jacob Skaria" wrote: Post sample data and let us know which column do have the cases (starting with A/B).. -- Jacob (MVP - Excel) "Bradly" wrote: I have the following code which filters the cases for a particular case manager from an office report and pastes the data on a separate sheet for the case manager (case manager 141V for this example). Sub Get141V() ' ' Get141V Macro ' ' Sheets("Office").Activate Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("B1:B1000") 'Header in row Set CopyRange = Range("A1:M1000") FilterRange.AutoFilter Field:=1, Criteria1:="141V" CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("141V").Range("A3") Application.CutCopyMode = False Sheets("Office").Activate Selection.AutoFilter Application.Goto Reference:="R1C1" Sheets("141V").Activate Application.Goto Reference:="R1C1" End Sub The cases either start with an A or a B. With this code, all A and B cases are pulled and listed together. Is there any way to modify this code to where it would filter for the case manager number AND all B cases only? What I would like to do is this: instead of listing all A and B cases together, I want to just list the B cases. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIF Statement combining 2 different pieces of data | Excel Programming | |||
Counting 2 different pieces of data in 2 different columns | Excel Worksheet Functions | |||
How to display 2 pieces of data on one bar of a Pivot Chart? | Excel Discussion (Misc queries) | |||
Lookup Multiple Pieces of Data... | Excel Discussion (Misc queries) | |||
70 WORKBOOKS - NEED PIECES OF DATA FROM EACH TO FORM LIST | Excel Discussion (Misc queries) |