GETPIVOTDATA in Macro

[pbapro2b]

I am newer to Macros, but I need help with this one. I know I cannot use =GETPIVOTDATA(blah.blahblah) as a formula in a macro. I have been searching this site and google for an example of what I am trying to accomplish.

Here is my situation. I need to report on service ticket SLA's on a weekly basis. My predecessor created a calculator sheet so I just need to copy and paste my ticket data into the sheet, refresh the pivot tables and it gives me the needed percentages of SLA's met. The calculator sheet is clunky and requires formula edits based on number of tickets each week, so I built a macro to encompass all the needs. I have everything working except for his GETPIVOTDADA formula. Here is the formula I am trying to properly write into a Macro:

=GETPIVOTDATA("Due Date SLA",$A$3,"Due Date SLA","Met")/GETPIVOTDATA("Due Date SLA",$A$3)

Here is a snippet of the pivot table in question. Range A3:B6
Closed Tickets

Row Labels	Count of Due Date SLA
Met	42​
(blank)
Grand Total	42​

When I run the formula on this particular week, I get 100% (Which is correct). Can I get some advice to get me in the right direction? I've tried Double Quotes, and I have tried Carriage Returns, but I cannot seem to get it to function properly.

 

[ExcelToDAX]

Assuming you are putting the formula in the active cell, how about:

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""Due Date SLA"",R3C1,""Due Date SLA"",""Met"")/GETPIVOTDATA(""Due Date SLA"",R3C1)"

If this doesn't work, I would copy that exact formula you have, record a macro, and paste that formula and click Stop Recording. And then the Macro should show the R1C1 way of getting the function.

Turn on the Developer tab if you don't have it on already so you can record a macro and stop recording it.

 

[pbapro2b]

Thank you! my issue was the A3 needed to be replaced with R3C1.

Much appreciated!!