I'm reluctant that someone will be able to figure this out, but here it goes:
When clicking my listbox items (MultiSelect LisbBox), it populates various calcuated fields in my pivot table. I'm trying to figure out how to Remove and Add my calculated field items when they are selected in the listbox. The code below runs without errors, and it populates the pivot field with the desired calculated fields. However, when I DESELECT the 2nd listbox item, it does not remove the calculated field. If I deselect the 1st listbox item, then ALL of the calculated fields will be removed from the pivottable. How do I modify the code below so that each calculated field is removed individually when it is deselected from the listbox.
Many thanks in advance if someone can solve this!
Private Sub ListBox8_Change()
'$CHANGE from Standard/T1 Cost
Application.ScreenUpdating = False
Dim i As Long
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
With ListBox8
For i = 0 To .ListCount - 1
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pfNew As PivotField
Dim strSource As String
Dim strFormula As String
If .Selected(0) Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("STANDCHG$ 12"), Sheets("SCodes").Range("AF3").Value, xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AF3").Value)
.NumberFormat = "$#,####0.0000"
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("T1CHG$ 12"), Sheets("SCodes").Range("AG3").Value, xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AG3").Value)
.NumberFormat = "$#,####0.0000"
End With
Else
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.CalculatedFields
strSource = pf.SourceName
strFormula = pf.Formula
pf.Delete
Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
Next pf
End If
'Next Month
If .Selected(1) Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("STANDCHG$ 11"), Sheets("SCodes").Range("AF4").Value, xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AF4").Value)
.NumberFormat = "$#,####0.0000"
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("T1CHG$ 11"), Sheets("SCodes").Range("AG4").Value, xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("SCodes").Range("AG4").Value)
.NumberFormat = "$#,####0.0000"
End With
Else
'ISSUE HERE WITH NOT DISPLAYING A DIFFERENT MONTH OF DATA (Sheets("SCodes").Range("AF4") AND ("AG4")
For Each pf In pt.CalculatedFields
strSource = pf.SourceName
strFormula = pf.Formula
pf.Delete
Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
Next pf
End If
Next i
End With
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
End Sub
It looks like different things happen when I move around the position of this line of code:
Set pt = ActiveSheet.PivotTables(1)
but I'm not sure if this is the issue.
Bookmarks