Drop lists to control pivot table

I am trying to compile a sales report in which end-user(s) can make various choices on what they want to valuate/view.
In attachment you will find a table I worked out (on “Desired Results”-tab) which works perfectly the way I want the result to be. Due to the numerous formulas the document is very slow (calculation times up to 15min!) and is the file size too big for the end-user(s).
Because of the size of the source data, +65K rows and growing, I looked at bringing in the data into a Data Model pivot table which would make calculations a lot faster and reduces the file size drastically.
In this pivot table I want the end-user(s) be able to choose in the drop-down list in cell B2 the view-type in the row field being Agent, Consultant, Originator or Property. Problem is that source for each selection is situated in a different column in the data. I have scoured the net for days and came to a solution in taking out all the row field data and inserting the selection back in with vba. I found the here below code of Debra Dalgeish to remove all field rows

Please Login or Register  to view this content.

but ran into trouble with the “pf.Orientation=xlHidden” which gave me run-time error '1004': unable to set the orientation property of the pivot field class as a result. I suspect this has got something to do with the fact that I use a DM-pivot instead of a “normal” pivot but didn’t found a work-around this issue.
Secondly, I would like to be able have a selector box (like a slicer) in cell D2 which eventually should show as a sum of the selections made in that selector box in the data field. Moreover, would it be possible to add in restrictions on certain combinations of selections please?
Could anyone help me in my automation ordeal or are there perhaps better suitable solutions for what I am trying to achieve please?

Thank you very much dflak,

Simple yet elegant! I like it a lot!!!

For part 2 I don't want the end-user to be able to combine for instance bed nights with sales revenue (basically all the other categories in the Tbl_Help) because of it's nature. Bed nights are a count of events happening whereas the others are sales revenue. In order to avoid mistakes and misunderstanding at the end-user's side I want to prevent them to be able to make combinations that are "non-compatible" and non-relevant.

It is indeed prohibitively slow as you mentioned. However, I do understand the mechanism you have built-in and believe that this is the way to go forward.
Thank you very much dflak