Filtering based on row

I have a worksheet with 100 or more rows of data.

Each row is data for a regional manager and their agency. Each
regional manager can have multiple agencys (in different states for
example) and thus each regional manager can have multiple rows of data.

The worksheet is called Sorted Rankings. The regional manager's name
is in column P.

Can I filter out each regional manager and then copy their records to a
seperate spreadsheet?

Please post any code that might be of help.

Thanks,
Brian

Hi,

This code will copy all entries relating to Mr Manager to sheet 2 of your
workbook.

It could obviously be tweeked but this is a starting point for you.

Andi

Sub Test()
Dim x As Long
Dim y As Long
y = 1
For x = 1 To 65536

If Cells(x, 16).Value = "Mr Manager" Then
Cells(x, 1).EntireRow.Copy
Sheets("Sheet2").Cells(y, 1).PasteSpecial
y = y + 1
End If
Next x

Application.CutCopyMode = False

End Sub

"BerkshireGuy" <[email protected]> wrote in message
news:[email protected]...
I have a worksheet with 100 or more rows of data.

Each row is data for a regional manager and their agency. Each
regional manager can have multiple agencys (in different states for
example) and thus each regional manager can have multiple rows of data.

The worksheet is called Sorted Rankings. The regional manager's name
is in column P.

Can I filter out each regional manager and then copy their records to a
seperate spreadsheet?

Please post any code that might be of help.

Thanks,
Brian

"BerkshireGuy" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet with 100 or more rows of data.
>
> Each row is data for a regional manager and their agency. Each
> regional manager can have multiple agencys (in different states for
> example) and thus each regional manager can have multiple rows of data.
>
> The worksheet is called Sorted Rankings. The regional manager's name
> is in column P.
>
> Can I filter out each regional manager and then copy their records to a
> seperate spreadsheet?
>
> Please post any code that might be of help.
>
> Thanks,
> Brian
>

Snippet of a similar report seperating sales to different pages for 3
salesmen which you might find helpful.


//snip//
Application.DisplayAlerts = False 'turn off screen for speed
'copy data to working space since jic macro goes wrong
Sheets("Sorted Rankings").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "temp"

'create and label sheet for each manager
Sheets.Add.Name = "Albert"
Sheets.Add.Name = "Tom"
Sheets.Add.Name = "Jim"

'copy title row
For Each WS In Worksheets(Array("Albert", "jim", "Tom"))
WS.Range("A1:P1").Value = Sheets("Print").Range("A1:P1").Value
Next

'seperate data by manager
Sheets("Temp").Activate
For Each cell In Range("P1:P" & Range("P65536").End(xlUp).Row)
Select Case Ucase(Trim(cell.Value))
Case "ALBERT":
cell.EntireRow.Cut Sheets("Albert").Range("A65536").End(xlUp).Offset(1,
0)

Case "JIM":
cell.EntireRow.Cut Sheets("Jim").Range("A65536").End(xlUp).Offset(1, 0)

Case "TOM":
cell.EntireRow.Cut Sheets("Tom").Range("A65536").End(xlUp).Offset(1, 0)
End Select
Next
Sheets("temp").Delete 'remove now empty working space
without bothering user
Application.DisplayAlerts = True 'turn screen back on
//snip//