Excel VBA string variable as criteria in the GetPivotData method?

[knifey]

Hi,
I'm having great difficulty in trying to build a string to use as criteria in the GetPivotData method (not finding the data in the pivot table). Can anyone advise where I'm going wrong?
Any help will be much appreciated.
Thanks,
K

My code:
' Get PivotData for total number of patients in April for chosen contract number.
On Error Resume Next
Set rngTableItem = ActiveCell. _
PivotTable.GetPivotData("PATIENT NAME (PLEASE USE CAPITALS)", "Daylist Month Name", "April", pivotStr)
wb1.Worksheets("Contract").Range("G3").Value = rngTableItem.Value
Set rngTableItem = Nothing

pivotStr contents in immediate window:
"Contract Number", 198226 Happy Teeth Hackney

pivotStr in VBA code:
pivotStr = Chr(34) & "Contract Number" & Chr(34) & ", " & contractVal

 

[SkipVought]

Hi,

Couldn't you aggregate directly from the PT source data? I'd much rather use everyday tools, SUMIFS, COUNTIFS, SUMPRODUCT etc., than have to work with a seldom-used set of PT tools.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?
PivotTable.GetPivotData("PATIENT NAME (PLEASE USE CAPITALS)", "Daylist Month Name", "April", "Contract Number", contractVal)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[knifey]

Hi PHV,
That's the exact same code I originally had. My problem is I need:

"Daylist Month Name", "April", "Contract Number", contractVal
&
"Daylist Month Name", "April", "Contract Number", contractVal, "Location/Project", projectVal
&
"Daylist Month Name", "April", "Contract Number", contractVal, "Location/Project", projectVal, "Clinician Name", clinicianVal

...depending on what choice the user makes from a validation list.
Thanks,
Roy

 

[PHV]

And the problem is ?

 

[knifey]

Hi PHV,
I have inherited this VBA system and it has approx 1000 getpivotdata lines.
Rather than having an if statement for each line. I thought it would be better to just change the criteria depending on the users choice. Aside from anything else this would save me a whole bunch of time.
Is an if statement the only way to go?
Thanks,
Roy

 

[PHV]

You may use arrays or collections or dictionaries or ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886