formulas based on pivot table cells not dynamic && GetPivotData form not flexible

I'm kind of a novice at pivot tables. I'm making this massive macro (to me anyway) that takes a "Practice Management" employee client service report, formats it and spits out a pivot table on a new worksheet. It's been going really well actually. Functions are calling other functions and it's beautiful.

On the pivot table worksheet, it looks pretty good too. The manager wants a range of cells to the right of the table to be prepopulated & preformatted, shaded and to include formulas referencing some cells in the table and other cells the manager manually enters for prior year to current year comparisons. I can do it all until I get to the formula that has to reference a cell in the pivot table

I'm kind of new to how pivot tables work. Why can't it be simple enough that I can just make a formula as you normally would (a2-a1)? Instead you choose a cell in the table and it spits out this:

Please Login or Register  to view this content.

When I ran that, in a loop, I thought this was good but turns out it is not dynamic. It doesn't change the value based on the row. All values were the same as row 1 of the table.


I understand pasting the PT as a value is an option. I'd consider that if I had to bu I don't want to.

What is with this GETPIVOTDATA function? Why can't it just refer to cell K7?

Then I thought I'd copy the value of the first cell in each row (where the table is rooted) and insert that in the right spot of the formula as an argument in that big formula. I thought I knew how to do this king of thing. I think pivot tables throw everything off. It didn't work for whatever reason, so instead I just declared a few variables

dim cellcontents (= the value in A1, so in our first case it would be "401 Other General Procedures" but in our second case, not shown here, it should say "401 Planning" and so on.
dim stringtopaste


I had these two lines of code:

Please Login or Register  to view this content.

I played around with quotes in case it was something like that. Basically it's not dropping the value of stringtopaste into the formula. I can see in my locals window it is what I want it to be, or close. It either gives me an error [Application defined or object defined error] or with quotes just pastes the name of the variable, not it's contents "stringtopaste", so that's useless.

I don't even know if this is the best approach to making the pivot table references dynamic. All open to ideas, whatever works, whatever is the simplest.

Pivot tables are powerful and I like them, but why are they so funky with formulas and working with VBA?

ps you can see my entire code on
https://github.com/ryanpotato/VBA-fo...ob/master/main
it might not be absolutely up to date wrt the questions in this post, not sure when last committed, but you get the gist.