VBA to split commissions from one sheet to multiple sheets and summarize by account type

I tried to organize the data in the way that dflak did. For some reason, the macro wants to pull the headers from row 10 of the Data 2 tab and I can't find in the code where to change that, so that is why the data is shifted down that far on the Data 2 tab. I've successfully organized the data in a way that the existing macro is able to create new tabs for each salesperson as long as there aren't any cells with formulas in them below the data set. I tried to follow dflak's ideas but would like to avoid the pivot table route if that's possible. Is there any way to make the new tabs from the macro create something similar to the Ideal Report?

Would it be easier to create a macro for the report per salesperson from my original file or from the file attached to this post? I'm open to suggestions.

I appreciate anything that can help me find a solution.