"Tim879" wrote in message
ups.com...
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

Thanks Tim for that comprehensive reply.

It would seem that the effect of not ticking the "Save data with data
layout" is that data is lost. If, therefore, the primary data is not lost
(either the spreadsheet is deleted or the Pivot Table transferred to another
file) there seems to be no practical reason for ticking SDWDL.

Perhaps someone could confirm this.

Thanks.

Bill Ridgeway