Add Data Field in Pivot Table

[Anthon]

Hello,

I need some advice on how I can add data field in Pivot Table. I've attached the file for reference. Can someone explain to me why there is an error when I run the Subroutine UpdatePivot? Where did I go wrong? The Pivot Table is in the sheet "Stockist". Thanks in advance!


Sub UpdatePivot()
Dim ws As Worksheet, SrcData As String, pvtCache As PivotCache
Dim ws2 As Worksheet, NR As Long, NC As Long, ws3 As Worksheet
Dim pf As PivotField, pt As PivotTable, df As PivotField, str As String
'Set ws = ThisWorkbook.Worksheets("Lisun Data")
Set ws2 = ThisWorkbook.Worksheets("Cover")
Set ws3 = ThisWorkbook.Worksheets("Stockist")
Set pt = ws3.PivotTables("PivotTable3")
'NR = ws.Range("A" & Rows.count).End(xlUp).Row
'NR = NR - 1
'SrcData = ws.Name & "!" & Range("A1:" & ws2.Cells(2, 6) & NR).Address(ReferenceStyle:=xlR1C1)
'Set pvtCache = ThisWorkbook.PivotCaches.Create( _
' SourceType:=xlDatabase, _
' SourceData:=SrcData)
pt.RefreshTable
For Each pf In pt.ColumnFields
pf.Orientation = xlHidden
Next pf
str = ws2.Cells(6, 1) & "-" & ws2.Cells(6, 2)
pt.AddDataField pt.PivotFields(" Jun-17"), "Sum", xlSum
'With pt.PivotFields(" Jun-17")
' .Orientation = xlDataField
' .Function = xlSum
' .Position = 1
'End With
End Sub

[YasserKhalil]

Cross-Post thread ...
https://www.excelforum.com/excel-programming-vba-macros/1197698-need-to-change-pivotfield-name-based-on-a-cell.html

[Aflatoon]

You're using the data model so use Cubefields like this:


pt.CubeFields("[Measures].[Sum of May-17]").Orientation = xlDataField

for example.