Loop, copy, count

If I'm understanding your request correctly, I think you can achieve the results you want without VBA. In the 'Formula Solution' sheet of my attachment, I've used the following formulas:

In A4: =IF(OR(ISNUMBER(SEARCH("Scope",Source!A4)),Source!C4>0),IF(TRIM(Source!A4)="",A3,Source!A4),"")

In C4: =IF(Source!C4>0,Source!C4,"")

In D4, array-entered (Confirmed with Ctrl + Shift + Enter instead of Enter: =IF($C4="","",INDEX($A$4:$A4,MAX(IF(ISNUMBER(SEARCH("Scope",$A$4:$A4)),ROW($A$4:$A4)))-3))

In E4: =IF(Source!E4<>"",Source!E4,"")

Fill all of these formulas down 50 or so rows (or however many you think you'll need). The result is a table that will automatically update as you add or change data on 'Paste'. I've also added totals columns using the following in G5, array entered and filled down:

=IFERROR(INDEX($D$4:$D$50,MATCH(0,IF($D$4:$D$50="",1,COUNTIF($G$4:$G4,$D$4:$D$50)),0)),"")

And in H5, filled right and down (non-array): =IF($G5="","",COUNTIFS($D$4:$D$50,$G5,$E$4:$E$50,H$3))

It's a lot to enter, but it should provide a automatically updating "results" page without requiring VBA. Take a look at the attachment to see if it will work for you: