PDA

View Full Version : Solved: Pivot Table refresh issue



OhGorgeous1
10-29-2008, 01:39 AM
Hi Guys, I have a small issue with the refreshing of a pivot table on a protected sheet.

When protecting the sheet I have put the tick in 'Use PivotTable reports' but when the below code runs (and it does what it says on the box!) the 'Use PivotTable reports' bit drops therefore not allowing the user to drill down.

Sub Refresh()
ActiveSheet.Unprotect ("***")
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveSheet.Protect ("***")
End Sub

Any ideas please.

:dunno

Bob Phillips
10-29-2008, 01:56 AM
Not clear what the problem is. Can you post a workbook?

OhGorgeous1
10-29-2008, 02:42 AM
Not clear what the problem is. Can you post a workbook?

Sorry the workbook has sensitive data in it, I will try and explain a little better.

I have a worksheet with a pivottable (that you can drill down on) the sheet is protected with a password. When you set the protection you get a list of boxes you can tick I have put a tick in 'Select unlocked cells' and 'Use PivotTable reports' also on the sheet is a button that runs the macro as mentioned before to update the pivottable. When this is clicked and the table updates you are then unable to use the dropdown on the pivottable.

Hopefully that makes more sense.

Bob Phillips
10-29-2008, 04:48 AM
I can only assume your code is being invoked which loses sthose settings.

Try



Sub Refresh()
With ActiveSheet
.Unprotect ("***")
.PivotTables("PivotTable4").PivotCache.Refresh
.Protect Password:="***", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowUsingPivotTables:=True
.EnableSelection = xlUnlockedCells
End With
End Sub

OhGorgeous1
10-29-2008, 06:02 AM
Thanks so much XLD that worked great!

Many thanks for all the help you have provided

Niki