Refresh Pivot Tables

[Guest]

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.

 

[Tom Ogilvy]

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. )

 

[Guest]

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

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. )

 

[Guest]

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.

 

[Tom Ogilvy]

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
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
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. )

 

[Guest]

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?