Refresh Pivot Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VJ - 06 Aug 2004 19:33 GMT

Hi

Is there any way I can automatically open and unprotect , refresh and then
protect a Pivot table through VBA macro?

Pls help me out.

Thanks in advance.

Reply to this Message

Tom Ogilvy - 06 Aug 2004 19:49 GMT

Use the
Activesheet.EnablePivotTable
Activesheet.Protect Password:="ABC", UserInterfaceOnly:=True
Activesheet.PivotTables(1).RefreshTable

This must be done each time the workbook is opened as the EnablePivotTable
and UserInterfaceOnly settings are not persistent across the closing of a
workbook (so use the workbook open event as an example. )

Signature

Regards,
Tom Ogilvy

> Hi
>
[quoted text clipped - 4 lines]
>
> Thanks in advance.

Reply to this Message

VJ - 06 Aug 2004 20:37 GMT

Thanks Tom. It works!!!!!!!!

> Use the
> Activesheet.EnablePivotTable
[quoted text clipped - 14 lines]
> >
> > Thanks in advance.

Reply to this Message

Gordon Humphreys - 14 Sep 2006 16:45 GMT

I have the same question as VJ, but want the macro to work on any pivot table
by first selecting a cell within that table [I'll do a button nearby each
table to run it] ie similar to the standard ! button.

For me, Tom's code comes out a ActiveSheet.Enable.... [does the Capital S
matter?] and results in error 1004.

I would be similarly grateful for a steer.

> Use the
> Activesheet.EnablePivotTable
[quoted text clipped - 14 lines]
> >
> > Thanks in advance.

Reply to this Message

Tom Ogilvy - 15 Sep 2006 02:02 GMT

VBA is case insensitive for names, variables

Here is a copy of the help example for EnablePivotTable

EnablePivotTable Property Example

This example enables PivotTable controls on a protected worksheet.

ActiveSheet.EnablePivotTable = True
ActiveSheet.Protect contents:=True,
userInterfaceOnly:=True------------------------------------if you have excel
2002 or later, you can set this manually through the sheet protection menu
(under tools) and it will persist as long as it is opened only in xl2002 or
later-- Regards,Tom Ogilvy

>I have the same question as VJ, but want the macro to work on any pivot
>table
[quoted text clipped - 25 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Gordon Humphreys - 20 Sep 2006 09:44 GMT

My thanks to Ogilvy et al.
This seems to work fine - select any pivot table first [the separate protect
macros are to keep the password hidden in only one place]

Sub PtRefresher()
Application.Run Range("Unprotecter"
ActiveSheet.EnablePivottable=True
ActiveSheet.Pivottables(1).RefreshTable
MsgBox "Pivot Table Refreshed"
Application.RunRange("Protecter")
End Sub

However, it don't work if a derived PT is on a nearby locked sheet. Is
there any way round? I have recently decided to avoid derived sheets and
always start from scratch: is this the only way?

Is there any way of pasting stuff into these handy messages, for slow typists?

Reply to this Message