How to update hidden password protected pivots?

[kterkuile]

Hi all,

I stumbled over this thread http://www.mrexcel.com/forum/showthread.php?t=443881 when looking for a solution to my problem.

Mine is similar, but a bit more complicated.

I have a number of sheets in my workbook each containing 1 pivot table. Some sheets are hidden others are not. They are all password protected with the same password. They are all based on the same data in the workbook. What i want is to update them all. For that i need to remove the password protection and reinstate it when i've updated and finally hide the sheets that were hidden before and don't hide the ones that that weren't hidden before.

Private subs are no option because i do't want to approach the hidden pivot tables and therefore won't trigger the private sub (i guess at least).

Could any of you VBA-magicians give n00by a push in the right direction?

Thanks guys,

Karel ter Kuile

 

[arkusM]

Give this a go:

Sub ReFresh_Pivot()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Dim pt As PivotTable
    Dim mysheet As Worksheet
    'UnFilters Sheets
    For Each mysheet In ActiveWorkbook.Worksheets
        mysheet.Unprotect "ENTER PASSWORD"
            For Each pt In mysheet.PivotTables
                pt.RefreshTable
            Next pt
        mysheet.Protect "ENTER PASSWORD"
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Hi all,

I stumbled over this thread http://www.mrexcel.com/forum/showthread.php?t=443881 when looking for a solution to my problem.

Mine is similar, but a bit more complicated.

I have a number of sheets in my workbook each containing 1 pivot table. Some sheets are hidden others are not. They are all password protected with the same password. They are all based on the same data in the workbook. What i want is to update them all. For that i need to remove the password protection and reinstate it when i've updated and finally hide the sheets that were hidden before and don't hide the ones that that weren't hidden before.

Private subs are no option because i do't want to approach the hidden pivot tables and therefore won't trigger the private sub (i guess at least).

Could any of you VBA-magicians give n00by a push in the right direction?

Thanks guys,

Karel ter Kuile

 

[kterkuile]

It works brilliantly. The hidden/not hidden issue isn't an issue at all. What's hidden stays hidden and what's not not. Thanks so much. You know what that makes you, don't you, you magician you?

Take care & thank you so much,

Karel

 

[arkusM]

It works brilliantly. The hidden/not hidden issue isn't an issue at all. What's hidden stays hidden and what's not not. Thanks so much. You know what that makes you, don't you, you magician you?

Take care & thank you so much,

Karel

almost everything I know was learned here, keep coming back to these forums and asking. Eventually a TRUE magician will help you. They will blow your socks off, I simply stopped wearing socks to avoid picking them up all the time!