Copy all rows between two values where the first value is variable

[abbasab]

0
down vote
favorite
I want to loop and extract all the rows between two different words (For Example, 56050067 and 56050068) that repeat in a column. This code get me just the first range

Thank you for intance

Code:

Sub copy()

Dim rownum As Long Dim colnum As Long Dim startrow As Long Dim endrow As Long Dim lastrow As Long Dim s As Range Dim e As Range rownum = 1 colnum = 1

lastrow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row

With Sheets("Sheet1") 'or this can be any other sheet where you search

Set r = .Range("B:B").Find("56050067")
If Not r Is Nothing Then
Set e = .Range("B:B").Find("56050068", r).Offset(-1)
If Not e Is Nothing Then

.Range(r, e).EntireRow.copy Sheets("Sheet2").Range("A1") 'or to whatever sheet
End If
End If
End With

End Sub

 

[Fluff]

Cross posted https://stackoverflow.com/questions...-two-values-where-the-first-value-is-variable

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.