Refreshing a Pivot Table on a Protected Sheet
In Excel 2003, you protected a worksheet, and in the list of options, added a checkmark to allow all users of the worksheet to use PivotTable reports.
However, you can’t create a new pivot table, or refresh a pivot table on the worksheet because some buttons on the PivotTable toolbar are disabled.
When you protect a worksheet and allow use of PivotTable reports, you can work with existing PivotTable reports. You can’t create a new PivotTable report on the protected sheet, or refresh existing pivot tables.
To refresh the pivot table, you can temporarily unprotect the worksheet, refresh the pivot table, then protect the sheet. You can do this manually, or record a macro, then run that macro to refresh the pivot table.
___________________________
For more information on pivot tables, see the PivotTable Topics on my Contextures web site.
Typically one wants to enable clients to enter data in specific areas and then be able to evaluate the results, through refresh. Is there absolutely no way around this without using VBA?
Giel, you could leave the pivot table sheet unprotected, so it can be refreshed. Protect the data entry sheet, leaving the cells unlocked where the users can make changes.
Thx. The option I was looking for to have pivot table and data entry in the same sheet. That is not possible so perhaps I should use ‘view’ “new window” to allow for data entry and evaluation of results at the same time. giel
I am using a macro to refresh a Pivot Table and I am getting an error. It stops running at this point
” ActiveSheet.PivotTables(“PivotTable5″).RefreshTable”
I am working with Excel 2010
@Saswati, your pivot table probably has a name other than “PivotTable5″
If there is only one pivot table on the sheet, change the code to use the index number, instead of the name:
ActiveSheet.PivotTables(1).RefreshTable
@Debra, but it worked fine on Excel 2003
@Saswati, was the code in the same file, for the same worksheet? It should work fine in Excel 2007 too, but try using the index number, instead of the pivot table name, in the code.
@Deb
Still giving the same error at the same place, here is the whole code
Sub RefreashCSQF()
‘
‘ RefreashCSQF Macro
‘ refreash Qualified Change Sheet pivot table
‘
‘
Application.Goto Reference:=”hyperlink”
Range(“B1″).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.Goto Reference:=”Date”
Selection.Copy
Windows(“SAM.xls”).Activate
Application.Goto Reference:=”Date”
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:=”CSQF”
Application.CutCopyMode = False
ActiveSheet.PivotTables(1).RefreshTable
Range(“A1″).Select
Windows(“Account Summary.XLS”).Activate
ActiveWorkbook.Close
End Sub
Hi,
I’m using the below mentioned macro to refresh all the pivot tables in a worksheet. The macro is running successfully in unprotected sheet, but I need to run it in protected sheet by using a shortcut key. Please suggest
Sub AllWorksheetPivots()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub