Excel Pivot Table Refresh Question

Hi,
I hope there is someone that can help me with another question
regarding refreshing Pivot tables

I have a spreadsheet with:
-Some worksheets storing lookup information and a data input sheet
-A worksheet containing pivot tables showing results from the input and
lookup sheets
-A report worksheet with a summary of the data using Vlookup on the
Pivot tables worksheet, and performing additional calculations on this
pivot table derived data.

Whenever someone uses the report worksheet the data should be refreshed
from the Pivot tables as this is going to be a shared workbook with
possibly only one or two users occasionally entering data.

Have already tried solutions given to other's peoples questions without
success yet and don't know what I'm doing wrong (am using Excel 2000)

Have tried putting the following in "ThisWorkbook":

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ThisWorkbook.RefreshAll
End Sub

Am an Excel VBA newbie... Am also using Excel 2000 so wasn't sure if
this was the reason that this does not work.

Many thanks.

Regards,
David

Hi David,

I haven't seen the earlier questions and solutions you refer to, but ...

If I read your description correctly, you want the pivot tables to refresh
when a user selects the report worksheet. Your code should work (and both
SheetActivate and RefreshAll are available in Excel 2000), but your code
will refresh the pivot tables regardless of which sheet the user selects.
Depending on the number of pivot tables and the size of the data source,
that could get annoying, but it will work. I'd associate the RefreshAll
statement with a Worksheet_Activate event handler instead of a
Workbook_SheetActivate event handler.

I don't believe, though, that you can use RefreshAll in a shared workbook,
even if only one user actually has it open. And bear in mind that you can't
get at your VBA code if the workbook is shared. These are just a couple of
reasons that I don't like shared workbooks. It's a hassle in the short run,
but in the long run it works much better to store and edit the data in, say,
an Access database, and point pivot tables at that database. Handled that
way, each user can have a separate workbook and you avoid the sharing issue.
I suppose you could also save the report worksheet and the pivot tables in
separate workbooks, and point the pivot tables at the original workbook.
Either way, you could trigger a refresh using the Worksheet_Activate event.

C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"David D" <davidducat@yahoo.co.uk> wrote in message
news:1124886057.755732.16780@g49g2000cwa.googlegroups.com...
> Hi,
> I hope there is someone that can help me with another question
> regarding refreshing Pivot tables
>
> I have a spreadsheet with:
> -Some worksheets storing lookup information and a data input sheet
> -A worksheet containing pivot tables showing results from the input and
> lookup sheets
> -A report worksheet with a summary of the data using Vlookup on the
> Pivot tables worksheet, and performing additional calculations on this
> pivot table derived data.
>
> Whenever someone uses the report worksheet the data should be refreshed
> from the Pivot tables as this is going to be a shared workbook with
> possibly only one or two users occasionally entering data.
>
> Have already tried solutions given to other's peoples questions without
> success yet and don't know what I'm doing wrong (am using Excel 2000)
>
> Have tried putting the following in "ThisWorkbook":
>
> Option Explicit
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> ThisWorkbook.RefreshAll
> End Sub
>
> Am an Excel VBA newbie... Am also using Excel 2000 so wasn't sure if
> this was the reason that this does not work.
>
> Many thanks.
>
> Regards,
> David
>

Hi Conrad,

Thanks for the advice!

I used the Worksheet_Activate event in the relevant report worksheets
instead of using the Workbook_SheetActivate, as you suggested. That
seems to be a bit more efficient as this means anyone editing the data
sheets doesn't need to be annoyed by the Workbook refreshing needlessly
all the time. I hadn't checked whether they would work as shared
workbooks, and they do indeed result in error codes when I try to share
them using RefreshAll in the code.

The more I look into this I realise it would be much easier to use
Access as a backend db and just use Excel for reporting only.

Am on a bit of a learning curve with Excel and Access! Thanks for the
help again.

Regards,
David

Brent Primary Care Trust,
NHS. UK