Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows between two criteria
I have a workbook that I want to sort out. At the beginning of the
sort is a route name and at the end of the sort is "Zone Total" each time. What I want to do is sort all rows between these two rows and paste the correct routes in a corresponding worksheet. Example: Route 104 Data Data Data Customer Customer Customer Zone Total Data Data Data Route 105 Data Data Data Customer Customer Customer Customer Zone Total Data Data Data And so on All rows between route 104 and zone total will go on a sheet and all rows between 105 and zone total will go on a sheet. It will be the same routes everytime but with possible different customers. I know how to do simple loops but I am having trouble starting this. Any help will be much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows between two criteria
Something like this perhaps. You didn't provide the logic for which sheet
in which to paste so I just write in "'paste somewhere" for you to fill in. If you have trouble with that, provide the logic of what goes where and I'll massage it into the code. HTH Otto Sub SortEach() Dim rRoute As Range, rZone As Range Dim rColA As Range, rRngToSort As Range Set rRoute = Range("A1") Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp)) Do Set rZone = rColA.Find(What:="Zone Total", After:=rRoute, _ LookIn:=xlValues, LookAt:=xlWhole) Set rRngToSort = Range(rRoute.Offset(1), rZone.Offset(-1)).Resize(, 4) rRngToSort.Sort Key1:=rRngToSort(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range(rRoute, rZone).Resize(, 4).Copy 'Paste somewhere Set rRoute = rZone.Offset(1) Loop Until IsEmpty(rRoute.Value) End Sub "bpotter" wrote in message ... I have a workbook that I want to sort out. At the beginning of the sort is a route name and at the end of the sort is "Zone Total" each time. What I want to do is sort all rows between these two rows and paste the correct routes in a corresponding worksheet. Example: Route 104 Data Data Data Customer Customer Customer Zone Total Data Data Data Route 105 Data Data Data Customer Customer Customer Customer Zone Total Data Data Data And so on All rows between route 104 and zone total will go on a sheet and all rows between 105 and zone total will go on a sheet. It will be the same routes everytime but with possible different customers. I know how to do simple loops but I am having trouble starting this. Any help will be much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows between two criteria
I don't guess I understand completely.
I think I would like to use a do until loop that starts with Route 204 and copies all rows under it until it reaches a row called zone total. I am having trouble finding the cell that says "Route 204" and starting my loop from there. I have a total of 4 routes that will not change and I don't really want the code to be that dynamic in scope. The routes are 204, 205, 210, and 224. The sheets will be called Route 204, Route 205, ect... I think mainly what I need help on is how to start the loop from the cell that says Route 204 and then finding the "Route 205" cell and starting a new loop in there. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows between two criteria
The code I sent you does all that. It starts with A1 as being the cell that
contains the first Route XXX. Change that as needed. Then it looks for the first instance of "Zone Total" AFTER that first Route XXX. Then it works with that range. When that is done, it sets the first cell after the Zone Total cell as the next Route XXX cell. Then it repeats the loop. Is this not what you want? I added the code to paste the data. HTH Otto Sub SortEach() Dim rRoute As Range, rZone As Range Dim rColA As Range, rRngToSort As Range Set rRoute = Range("A1") Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp)) Do Set rZone = rColA.Find(What:="Zone Total", After:=rRoute, _ LookIn:=xlValues, LookAt:=xlWhole) Set rRngToSort = Range(rRoute.Offset(1), rZone.Offset(-1)).Resize(, 4) rRngToSort.Sort Key1:=rRngToSort(1), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range(rRoute, rZone).Resize(, 4).Copy Sheets(rRoute.Value).Range("A1").PasteSpecial Set rRoute = rZone.Offset(1) Loop Until IsEmpty(rRoute.Value) End Sub "bpotter" wrote in message ... I don't guess I understand completely. I think I would like to use a do until loop that starts with Route 204 and copies all rows under it until it reaches a row called zone total. I am having trouble finding the cell that says "Route 204" and starting my loop from there. I have a total of 4 routes that will not change and I don't really want the code to be that dynamic in scope. The routes are 204, 205, 210, and 224. The sheets will be called Route 204, Route 205, ect... I think mainly what I need help on is how to start the loop from the cell that says Route 204 and then finding the "Route 205" cell and starting a new loop in there. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows between two criteria
That is exactly what I needed. Sorry I just wasn't understanding.
Thank you very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Rows using criteria | Excel Programming | |||
Automatically copy rows based on criteria | Excel Discussion (Misc queries) | |||
Copy rows that meet a certain criteria | Excel Programming | |||
copy rows meeting criteria to another worksheet | Excel Worksheet Functions | |||
Copy Rows if Lookup Criteria Match | Excel Discussion (Misc queries) |