Pivot Tables -- Auto Refresh

[Aastha]

I looked at the submissions in the group, and the VB script solution
didn't work for me, hence this question.

Have a workbook in which the data is obtained from a database. This is
stored in one worksheet called "All Data". Another worksheet has a
pivot table which depends on the "All Data" worksheet. My problem. The
data in the "All Data" refreshes automatically each time I open the
workbook. However, for the refresh to work on the pivot table I have to
manually click the "Refresh all data" button. Is there any way to get
around that??? Please help.

Thanks.

Aastha

 

[Ken Wright]

Right click on the table, choose Table options and tick 'Refresh on open'

If for any reason that didn't work then you could use a piece of code such
as the following from Debra Dalgleish combined with the Workbook_Open event,
but you shouldn't need to.

Private Sub Workbook_Open()
'Debra Dalgleish
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------