Pivot Table VBA reference variable error

[meetdudhia]

Sub pivot2()

Dim PTCache As PivotCache
Dim pt As PivotTable
Dim XYZ As String
'On Error Resume Next

XYZ2 = Sheets("Action").Range("W1")
XYZ1 = Sheets("Action").Range("W2")
XYZ = Sheets("Action").Range("W3")
XYZ4 = Sheets("Action").Range("W6")


'create the cache
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion)

'Add new sheet
Worksheets.Add

'create the pivot table
Set pt = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=Range("A3"), _
TableName:="XYZ")


With ActiveSheet.PivotTables("XYZ").PivotFields(XYZ2)
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("XYZ").PivotFields(XYZ1)
.Orientation = xlColumnField
.Position = 1
End With

With ActiveSheet.PivotTables("XYZ").PivotFields(XYZ)
.Orientation = xlDataField
.Position = 1
.NumberFormat = "#0.0%"
.Calculation = "XYZ4"

End With
End Sub


Question: In calculation field I have reference (.Calculation = "XYZ4"), but VBA is giving me error, can any one tell me how to reference it so it can work with my list box.

I really appreciate any help.
thank you

 

[Andrew Poulsom]

What's in Sheets("Action").Range("W6"). You need to remove the double quotes around "XYZ4" and XlPivotFieldCalculation can be one of these XlPivotFieldCalculation constants:

xlDifferenceFrom
xlIndex
xlNoAdditionalCalculation
xlPercentDifferenceFrom
xlPercentOf
xlPercentOfColumn
xlPercentOfRow
xlPercentOfTotal
xlRunningTotal

 

[meetdudhia]

Yes in Action Sheet W6 I have xlPercentOfTotal but VBA code is still giving me error.

Code is not able to Reference to Cell W6 on Action sheet.

 

[Andrew Poulsom]

You need to use the constant's value, which is 8 (put that in W6). You can't pass a string and expect it to be converted to a constant with the same name.

 

[meetdudhia]

With ActiveSheet.PivotTables("XYZ").PivotFields(XYZ)
.Orientation = xlDataField
.Position = 1
.NumberFormat = "#0.0%"
.Calculation = XYZ4


But still its not working it is giving error "Unable to set calculation property of pivot field class"


Please Help.

 

[Andrew Poulsom]

See my reply in post #4.

 

[meetdudhia]

It worked by inserting number "8" but I am creating macro for someone else....so it would be hard to understand numbers like 6 ,7, 8 and it is easy to understand text like
xlPercentOfColumn
xlPercentOfRow
xlPercentOfTotal



Is there way to allow above text rather than number in list box???

 

[Andrew Poulsom]

You would have to use a lookup table to convert the string to a constant. The constants are:

xlDifferenceFrom 2
xlIndex 9
xlNoAdditionalCalculation -4143
xlPercentDifferenceFrom 4
xlPercentOf 3
xlPercentOfColumn 7
xlPercentOfRow 6
xlPercentOfTotal 8
xlRunningTotal 5

 

[meetdudhia]

Thank you Mr. Paulsom. I really appreciate your help.