Auto Format PivotTables to Match Source Data (Power Tips Series)

Today’s author is Mike Alexander, a Microsoft Excel MVP who shows us how to run a Stored Procedure to get data from a SQL server. For more information, visit www.datapigtechnologies.com.

So summer is for practically over and I'm back to blogging topics that can actually help people. For my first post back, I'll come out big with one of the best pieces of code I've ever written.

A few weeks ago *** Kusleika posted a small but brilliant piece of code that auto formats the active pivot data field to a number format without having to muddle through the formatting dialog boxes. I loved it and used it for a while. Then I thought:

That is to say, I want my pivot table to automatically recognize the number formatting in the source and apply it without my help. So I got to work and created what you're about to see here. You had better put on your sunglasses because the awesomeness is blinding.

Start with a raw pivot table similar to the one shown here. Notice how Excel slaps the General format on numbers. Also note the always annoying "Sum of" prefix on every value field.

Now just place your cursor inside the pivot table and run this code:

Sub AdoptSourceFormatting()
'Mike Alexander
'www.datapigtechnologies'
'Be sure you start with your cursor inside a pivot table.

Dim oPivotTable As PivotTable
Dim oPivotFields As PivotField
Dim oSourceRange As Range
Dim strLabel As String
Dim strFormat As String
Dim i as Integer

On Error GoTo MyErr

'Identify PivotTable and capture source Range
    Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
    Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))

'Refresh PivotTable to synch with latest data
    oPivotTable.PivotCache.Refresh

'Start looping through the columns in source range
    For i = 1 To oSourceRange.Columns.Count
   
    'Trap the column name and number format for first row of the column
        strLabel = oSourceRange.Cells(1, i).Value
        strFormat = oSourceRange.Cells(2, i).NumberFormat
   
    'Now loop through the fields PivotTable data area
        For Each oPivotFields In oPivotTable.DataFields
  
        'Check for match on SourceName then appply number format if there is a match
            If oPivotFields.SourceName = strLabel Then
            oPivotFields.NumberFormat = strFormat
           
        'Bonus:  Change the name of field to Source Column Name
            oPivotFields.Caption = strLabel & " "
            End If
       
        Next oPivotFields
    Next i

Exit Sub

'Error stuff
MyErr:
If Err.Number = 1004 Then
MsgBox "You must place your cursor inside of a pivot table."
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If

End Sub

In seconds, your pivot table will be reformatted to match the number formatting and labeling found in the source data.

The code is commented relatively well so I won't bother explaining every detail. But here's the gist.

The code simply loops through each column in the data source, capturing the header name and the number format of the first value under each column. Once it has that information, it determines if the associated PivotField is in the Values Area of the PivotTable. If it is, the number format and label is applied to that PivotField.

I'll be expecting a call from the Nobel institute later this week.