Solved: Change PivotTable Data Field in VBA

[f2e4]

Dim StaffMember As String
Dim StartDate As Date
Dim temp as Long

'Weekending Date
StartDate = Date + 6 - Weekday(Date)

temp = ListBox1.ListIndex
With ListBox1
StaffMember = .List(temp, 1) & " " & .List(temp, 0)
End With

'Update PivotTable fields
Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Staff Member").CurrentPage = StaffMember
Sheets("Drop Downs").PivotTables("PivotTable1").AddDataField Sheets("Drop Downs").PivotTables("PivotTable1"). _
PivotFields(StartDate), "Sum of" & StartDate, xlSum
Sheets("Drop Downs").PivotTables("PivotTable1").DisplayNullString = False

ListBox1.Activate



The staffmember section works fine

However, the datafield always remains the same. How do I get this to actually change to the value I want it to?

[mae0429]

I'm a bit confused as to what exactly you want: can you clarify? Do you mean the main pivot cache where you're getting your data, or the column/row/page fields?

[f2e4]

I solved this one myself.

I wanted to totally clear all current pivot fields and add new ones.

All of my data fields were dates so that depending on the current week, the data fields changed to the current week value.

Anyway, this is the code I ended up using:


Dim pt As PivotTable
Dim pf As PivotField
Dim currentweek As String
Dim currentweek2 As String
Dim StaffMember As String
Dim temp As Long

'Current week value
currentweek = Sheets("Reports").Range("L47").Value
'Next week value
currentweek2 = Sheets("Reports").Range("L47").Value + 7

'Get value from listbox to change Pivottable filter
temp = Sheets("Reports").ListBox1.ListIndex
With Sheets("Reports").ListBox1
StaffMember = .List(temp, 0) & "," & " " & .List(temp, 1)
End With

For Each pt In Sheets("Drop Downs").PivotTables
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
With Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Project Name")
.Orientation = xlRowField
.Position = 1
End With
With Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Staff Member")
.Orientation = xlPageField
.Position = 1
End With
Sheets("Drop Downs").PivotTables("PivotTable1").PivotFields("Staff Member").CurrentPage = StaffMember
Sheets("Drop Downs").PivotTables("PivotTable1").AddDataField Sheets("Drop Downs").PivotTables( _
"PivotTable1").PivotFields(currentweek), Format(currentweek, "dd mmm"), xlSum
Sheets("Drop Downs").PivotTables("PivotTable1").AddDataField Sheets("Drop Downs").PivotTables( _
"PivotTable1").PivotFields(currentweek2), Format(currentweek2, "dd mmm"), xlSum
Sheets("Drop Downs").PivotTables("PivotTable1").DisplayNullString = False
End With
Next