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

_______________