filtering two pieces of data
in [Programming]
|
From: Bradly on 1 Jun 2010 21:10 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.
From: Jacob Skaria on 2 Jun 2010 00:16 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. >
From: Jacob Skaria on 2 Jun 2010 00:45 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. > >
From: Bradly on 3 Jun 2010 15:10 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. > > >
|
Pages: 1 Prev: Parsing a String - Call to Ron Rosenfeld Next: Need help in highlighting the cell color in VBA |