Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering based on row
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering based on row
"BerkshireGuy" wrote in message oups.com... 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// |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering based on Value | Excel Worksheet Functions | |||
filtering based on a value in a cell | Excel Worksheet Functions | |||
advanced filtering based on entries ABOVE those I wish to keep | Excel Discussion (Misc queries) | |||
Filtering data from one worksheet based on another | Excel Discussion (Misc queries) | |||
filtering list based on formating | Excel Programming |