Contextures Blog

Last week, Chandoo interviewed me for his Online Excel VBA School, and we talked about using VBA with Excel Pivot Tables.

If you drop a field into the Values area, and that field contains blank cells or text, it’s added as “Count of”, instead of “Sum of”.

With a macro, you can quickly change all the fields from Count to Sum, instead of fixing each pivot table summary function manually.

Select a Summary Function

One way to let users change the function is to create a drop down list of functions on the worksheet. Then, event code runs when the cell changes, and the selected function is shown in the pivot table.

The cell with the drop down list is named FuncSel, as you can see in the NameBox in the screen shot above.

On another sheet, that could be hidden from the users, there is a list of functions, and a formula that looks up the numeric value for each function. The cell with the formula is named FuncSelCode.

How It Works

When the FuncSel cell is changed, the Worksheet_Change code on that sheet runs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("FuncSel").Address Then
    ChangeAllData (wksLists.Range("FuncSelCode").Value)
End If
End Sub

The ChangeAllData procedure runs, using the numeric value in the FuncSelCode cell, and changes all the data fields in the pivot table.

Sub ChangeAllData(lFn As Long)
'changes data fields to selected function
On Error GoTo errHandler
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Application.ScreenUpdating = False
Set pt = wksPTSales.PivotTables(1)
On Error GoTo errHandler
pt.ManualUpdate = True
For Each pf In pt.DataFields
  pf.Function = lFn
Next pf
pt.ManualUpdate = False
exitHandler:
Set pf = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

Download the Sample File

To see the code, and test it with the sample data, you can download the Pivot Table Summary Function Change workbook.

The file is in Excel 2007 format, and zipped. Enable macros when you open the file.
______________

With the pivot table Show Details feature in Excel, a new sheet is inserted when you double-click on the value cell in a pivot table.

It’s a great feature for drilling into the details, but you can end up with lots of extra sheets in your workbook.

Usually, you don’t want to save the sheets, so you manually delete them before you close the file.

Automatically Name the Sheets

With event code on the pivot table’s worksheet, and in the workbook module, you can add a prefix – XShow_ – when these detail sheets are created.

That prefix should make the sheets easier to find and delete.

Automatically Delete the Sheets

To make the cleanup task even easier, you can use event code to prompt you to delete those sheet when you’re closing the workbook.

If you click Yes, all the sheets with the XShow_ prefix are deleted. Then, click Save, to save the tidied up version of the workbook.

See the Drilldown Sheet Code

For detailed instructions on adding the drilldown sheet naming and deleting code, visit the Excel Pivot Table Drilldown page on the Contextures website.

Download the Sample Drilldown File

To see how the event code names the sheets, and deletes them when closing, you can download the Pivot Table Drilldown sample file.
________________