Bill

If I understand the process correctly, saving data with your pivot
table actually makes a copy of your data and saves it with the pivot
table. For example if your pivot was based on a tab called data. After
creating your pivot, you could delete the data tab and still be able
to drill into your pivot table data since a copy of it was saved with
the pivot.

If you uncheck this option, a copy of the data is not saved, thus
making the file considerably smaller - especially if you work with
large data sets.

To find this option, right click on your pivot, go to table options -
Uncheck Save Data With Pivot Table Layout.

You could use the code below to cycle through all of your pivots in a
workbook and uncheck this option as well as refresh the pivots.

Sub Update_All_Pivots()
'Cycles through each page of a work book checking for pivots. Each
pivot found
'is refreshed

continue = MsgBox("This macro will update all pivots in the workbook.
Do you want to continue?", _
vbYesNo)

If continue = vbYes Then

Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String

On Error GoTo Error_Found

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_Update_All_Pivots


strResponse = MsgBox("Do you want to save data with pivot table
(larger file size)?", vbYesNo)

Application.ScreenUpdating = False

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
'ActiveSheet.PivotTables(iPivot).HasAutoFormat = False
ActiveSheet.PivotTables(iPivot).PivotCache.Refresh

If strResponse = vbYes Then
ActiveSheet.PivotTables(iPivot).SaveData = False
End If
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate

MsgBox ("Pivots updated successfully")

End If

GoTo Exit_Update_All_Pivots

Error_Found:
MsgBox ("Error Found. Macro ending.")
MsgBox Err & ": " & Error(Err)

Exit_Update_All_Pivots:
Application.CommandBars("PivotTable").Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

If continue = vbNo Then MsgBox ("Cancelled")

End Sub