Pivot Tables -- Auto Refresh

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

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 :-)
------------------------------*------------------------------*----------------


"Aastha" <[email protected]> wrote in message
news:[email protected]...
>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
>