Copy Data from one Sheet to other sheet based on condition

[SHEEPRAL]

The below data is in one excel sheet
now I want to copy all data for Exchange House ABC and XYZ into Urgent sheet
and rest to non-urgent sheet.
Pls note the row number may very from Exchange house,
I need to copy data from Exchange House name of the exchange house )
till row where Exchange House Total: ( amt ) in mentioned
header 1	header 2	header 3
Product Type: Credit to Account -
Exchange House: abc
Transaction Date:30 Oct 2017
1	1	1
Day Total: 123,523.00 (INR)
Exchange House Total: 123,523.00(INR)
Exchange House: pqr
Transaction Date:30 Oct 2017
2	2	2
Day Total: 5,000.00 (INR)
Exchange House Total: 5,000.00(INR)
Exchange House:xyz
Transaction Date:27 Oct 2017
3	3	3
Day Total: 262,305.00 (INR)
Transaction Date:30 Oct 2017
4	4	4
5	5	5
Day Total: 3,012,573.00 (INR)
Exchange House Total: 3,274,878.00(INR)

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[Michael M]

As I mentioned in your previous post, you have provided a small data set for us to work with !!!
This will work on the dataset provided, but I'm sure your "real" data may prove problematic !

Sub MM1()
Dim lr As Long, r As Long, sr As Long, er As Long, br As Long, fr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Urgent").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("Non Urgent").Cells(Rows.Count, "A").End(xlUp).Row
For r = 3 To lr
    If InStr(Range("A" & r).Value, "abc") Or InStr(Range("A" & r).Value, "xyz") Then
        sr = r
     End If
    If InStr(Range("A" & r).Value, "Exchange House Total") And sr <> 0 Then
        er = r
        lr2 = Sheets("Urgent").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(sr & ":" & er).Copy Sheets("Urgent").Range("A" & lr2 + 1)
        sr = 0
        er = 0
    End If
    If InStr(Range("A" & r).Value, "pqr") Then
        br = r
     End If
    If InStr(Range("A" & r).Value, "Exchange House Total") And br <> 0 Then
        fr = r
        lr3 = Sheets("Non Urgent").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(br & ":" & fr).Copy Sheets("Non Urgent").Range("A" & lr3 + 1)
        br = 0
        lr = 0
    End If
Next r
End Sub