VBA to Add Calculated Field and Measure into a Pivot Table

[PShingadia]

Hi:

I have the following code which I am trying to use to clear out the data fields in a pivot table and substitute with a data field selected by the user with a macro button. The code falls down on the very last line showing a 'Run time error 5 Invalid call or argument'. Please help!

Sub Add_Data_Field_PowerPivot_HiddenName()
'Add the Data field to the pivot table.
'The field is determined by the button text that calls the macro.


Dim pt As PivotTable
Dim shp As Shape
Dim cf As CubeField
Dim sField As String
Dim sTableName As String


'Set variables
Set pt = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text
sTableName = shp.AlternativeText
sField = "[" & sTableName & "].[" & sField & "]"

'Remove existing fields
For Each cf In pt.CubeFields
If cf.Name <> "Values" And cf.Orientation = xlDataField Then
cf.Orientation = xlHidden
End If
Next cf

'Add field that button was clicked for
pt.CubeFields(sField).Orientation = xlDataField


End Sub


Also how would this be amended to add a measure instead of a calculated field?