Copy rows between two criteria

[bpotter]

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.

 

[Otto Moehrbach]

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]

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.

 

[Otto Moehrbach]

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]

That is exactly what I needed. Sorry I just wasn't understanding.
Thank you very much.