|
Pivot tables wont work in protected sheet?
I use pivot tables in my spreadsheet.
When I protect the sheets to prevent changes to locked cells, the pivot tables wont allow data refresh?! Even when I check all the boxes when protecting a sheet - including the pivot tables check box, still the pivot tables cannot be activated. The pivot table cells are all unlocked as is the data it reads from. Can anyone help me? Tahnks, Roger |
Pivot tables wont work in protected sheet?
You can't refresh the pivot table if the sheet is protected, no matter
what protection settings you used. In the code you can unprotect the sheet, refresh the pivot table, then protect the sheet. Roger on Excel wrote: I use pivot tables in my spreadsheet. When I protect the sheets to prevent changes to locked cells, the pivot tables wont allow data refresh?! Even when I check all the boxes when protecting a sheet - including the pivot tables check box, still the pivot tables cannot be activated. The pivot table cells are all unlocked as is the data it reads from. -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Pivot tables wont work in protected sheet?
Thanks Debra,
Can you suggest code for me to use on a protected sheet - i can assign it as a macro to refresh the pivot table with a button by the pivot table? Best regards, Roger "Debra Dalgleish" wrote: You can't refresh the pivot table if the sheet is protected, no matter what protection settings you used. In the code you can unprotect the sheet, refresh the pivot table, then protect the sheet. Roger on Excel wrote: I use pivot tables in my spreadsheet. When I protect the sheets to prevent changes to locked cells, the pivot tables wont allow data refresh?! Even when I check all the boxes when protecting a sheet - including the pivot tables check box, still the pivot tables cannot be activated. The pivot table cells are all unlocked as is the data it reads from. -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Pivot tables wont work in protected sheet?
Sub RefreshPivot()
With ActiveSheet .Unprotect Password:="MyPwd" .PivotTables(1).RefreshTable .Protect Password:="MyPwd", _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True, AllowUsingPivotTables:=True End With End Sub Roger on Excel wrote: Thanks Debra, Can you suggest code for me to use on a protected sheet - i can assign it as a macro to refresh the pivot table with a button by the pivot table? Best regards, Roger "Debra Dalgleish" wrote: You can't refresh the pivot table if the sheet is protected, no matter what protection settings you used. In the code you can unprotect the sheet, refresh the pivot table, then protect the sheet. Roger on Excel wrote: I use pivot tables in my spreadsheet. When I protect the sheets to prevent changes to locked cells, the pivot tables wont allow data refresh?! Even when I check all the boxes when protecting a sheet - including the pivot tables check box, still the pivot tables cannot be activated. The pivot table cells are all unlocked as is the data it reads from. -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
| All times are GMT +1. The time now is 10:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com