filtering two pieces of data

B

Bradly

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.
 
J

Jacob Skaria

Post sample data and let us know which column do have the cases (starting
with A/B)..
 
J

Jacob Skaria

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
 
B

Bradly

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.
 

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


Top