VBA macro to find specific cells, copy the content and paste to ranges

Don't work with Excel much but have been asked to format an Excel report.

Users get an Excel report like the one attached. It has a blank column A. They get a summary of the user and their total and then it reports for the next rep, etc, etc. users want to filter various columns. They are having an issue tying the reps name to a particular row. They can see what ones belong to the rep but when filtered the rows get out of order and they can't also filter by the name. They want to tie the name to every row the rep that owns the record. The only way I know of doing this is to look for the cell that has the first name (Total for Smith,Bill: 10
), copy it, move up one row over to column A and paste the name onto every record above until they hit say a blank cell (case # let's say). Then it needs to look for the next rep name and do the same thing. Any ideas? Thank you

[SOLVED] Thank you so much!

Leith, do you know what might be the cause when I try and run it on my Excel? I get the error message..."Runtime error '91': Object variable or With block variable not set". When I debug it takes me to this line..."Set RngBeg = Rng.Cells(1, 1)".

Here is an example with the data cleared off and most of the rows removed. I kept 2 groups of records at the top and below is just some category rollups. The report will be of varying lengths depending the timeframe it is run. It comes to us as a flat file with no formulas, etc. xls.

Sorry about that. The first record starts at row 3 and shows their total at row 4. The 2nd record starts at row 5 (I guess I accidentally deleted off the name) and ends on row 27 (total), then gives a total for both records, and then at 29 lists the totals for each rep. Hope that clarifies it and thank you so much!