Copy Data from one Sheet to other sheet based on condition

[SHEEPRAL]

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

 

[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