Last year, I posted Excel VBA code for removing a calculated field from a pivot table.
The code works well if there is only one pivot table based on that pivot cache. However, a couple of comments mentioned that there were problems if multiple pivot tables shared the pivot cache.
Here is a revised version of the code, that checks for other pivot tables using the same pivot cache. If others are found, the macro is cancelled, and a message appears, listing the pivot tables.
Remove Calculated Fields for Non-Shared Pivot Cache
If other pivot tables in the workbook share the same cache, the macro is cancelled. A message lists those pivot tables.
Sub RemoveCalculatedFieldsNotShared() Dim ws As Worksheet Dim ptA As PivotTable Dim pt As PivotTable Dim pf As PivotField Dim pfNew As PivotField Dim strSource As String Dim strFormula As String Dim iPC As Long Dim lCache As Long Dim strPC As String Set ptA = ActiveSheet.PivotTables(1) iPC = ptA.PivotCache.Index For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables If pt.PivotCache.Index = iPC Then lCache = lCache + 1 strPC = strPC & ws.Name & " " _ & pt.TableRange2.Address _ & vbCrLf End If Next pt Next ws If lCache > 1 Then MsgBox "Cancelled" _ & vbCrLf & vbCrLf _ & lCache & " pivot tables share this pivot cache: " _ & vbCrLf & vbCrLf _ & strPC GoTo exitHandler Else For Each pf In ptA.CalculatedFields strSource = pf.SourceName strFormula = pf.Formula pf.Delete Set pfNew = ptA.CalculatedFields.Add(strSource, strFormula) Next pf End If exitHandler: Exit Sub End Sub
_______________
Are pivot caches shared across pivot tables by default? I have multiple pivots off a same range of data, which changes the # of rows daily. I used VBA to change the data source for the pivots, but the code works only if there is a single pivot table in the entire workbook. Any ideas why this is, and how to overcome this limitation? I use MS Excel 2007
Hi Debra,
Is there a way to Remove Calculated Fields for Shared Pivot Cache,
I am Using the following code to refresh the data fields in the my workbook, but i have problems with calculated fields, only one pivot table in my workbook is getting updated. can you let me know how to deal with this?
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField
Dim strField As String
Dim pfNew As PivotField
Dim strSource As String
Dim strFormula As String
strField = Range(“c35”)
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.CalculatedFields
strSource = pf.SourceName
strFormula = pf.Formula
pf.Delete
Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
Next pf
For Each pf In pt.DataFields
pf.Orientation = xlHidden
Next pf
With pt.PivotFields(strField)
.Orientation = xlDataField
.Function = xlSum
End With
Next pt
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub