Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Pivot Tables
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Pivot Tables
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. ) -- Regards, Tom Ogilvy "VJ" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Pivot Tables
Thanks Tom. It works!!!!!!!!
"Tom Ogilvy" wrote: 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. ) -- Regards, Tom Ogilvy "VJ" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Pivot Tables
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" wrote: 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. ) -- Regards, Tom Ogilvy "VJ" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Pivot Tables
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 "Gordon Humphreys" wrote in message ... 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" wrote: 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. ) -- Regards, Tom Ogilvy "VJ" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Pivot Tables
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh Pivot Tables with one hit | Excel Discussion (Misc queries) | |||
Cannot Refresh Pivot Tables | Excel Worksheet Functions | |||
Pivot Tables Refresh | Excel Discussion (Misc queries) | |||
Refresh multiple pivot tables | Excel Discussion (Misc queries) | |||
Pivot Tables -- Auto Refresh | Excel Discussion (Misc queries) |