Monday 5 August 2013

VBA Modules: Excel: xlPivots v1.00

Read this for full information on these modules
Does what it says on the tin.... dead simple stuff but pretty useful when you're in a hurry and have a workbook with 20 pivot tables.

'xlPivots
'v1.00 201x-xx-xx hh:mm

'===========================================================================
' HELP CONTACT
'===========================================================================
' Code is provided without warranty and can be stolen and amended as required.
'   Tom Parish
'   TJP@tomparish.me.uk
'   http://baldywrittencod.blogspot.com
'   DGF Help Contact: see BPMHelpContact module
'=========================================================================

'===========================================================================
'  xlPivots
'===========================================================================
' This module quickly enables/disables drilling/savedata in all pivots

'Version History:
'   v1.00

Option Explicit

Sub xlPiv_disable_drills()
'v1.00 201x-xx-xx hh:mm

Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        With pt
            .EnableDrilldown = False
        End With
    Next pt
Next sh

End Sub

Sub xlPiv_enable_drills()
'v1.00 201x-xx-xx hh:mm

Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        With pt
            .EnableDrilldown = True
        End With
    Next pt
Next sh

End Sub

Sub xlPiv_disable_save_data()
'v1.00 201x-xx-xx hh:mm

Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        With pt
            .SaveData = False
        End With
    Next pt
Next sh

End Sub

Sub xlPiv_enable_save_data()
'v1.00 201x-xx-xx hh:mm

Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        With pt
            .SaveData = True
        End With
    Next pt
Next sh

End Sub

No comments:

Post a Comment