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 Dick 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.
Hi Mike,
This is really useful stuff !
A couple of questions:
1. Why is the code put into a "Function" ?
2. If the pivot table is not refreshed, an error occurs, and the error message is misleading.
Is there a way to check whether the pivot table is refreshed or not ?
And if not, then do that and go further ?
Khushnood Viccaji
Mumbai, India
Also, I prefer the 'classic' pivot table layout, along with a few other default settings.
I have created a .bas module to setup a pivot table with the preferred default settings, based on a pre-defined data range name (which needs to be done first, manually).
Whenever I need to create a new pivot table, I import the .bas file and run it. Your code from this post will be a very useful extension to that code !
I can share that module in case anyone's interested.
Khushnood
Khushnood:
"Why is the code put into a Function?"
No reason really. I went ahead and changed the code so it can be used directly in the Macros window.
"If the pivot table is not refreshed, an error occurs..."
I'm not sure why this would happen. But just to be safe, I added a quick line that refreshes the pivot table before starting.
Cool! Shades are on
now if only someone could tell MS to stop defaulting value fields in pivot tables to "count of", as opposed to 'sum of' ..
or even better, tell MS to make the default value field a user definable option...I tend to use 'average of' in most of my pivot tables.
Hi Mike,
"I'm not sure why this would happen."
This is because in the pivot table's options, I had un-checked the option : 'Refresh data when opening the file'.
So when your code was run, it was trying to work with an 'un-refreshed' pivot table, and throwing an error.
"But just to be safe, I added a quick line..."
I did the same at my end
Khushnood
Hi Mike.
I´m Sergio From Spain.
I need some help from you.
I´m trying to use your code, but a continiusly have a error, The macro says me that I´m not in pivot table, when I actually I am, I review your code and I have a error on the folowing line:
Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))
I used a trik , I create a dinamic range o my sheet to refer the pivot table source then the code works. But it´s not a solution, because I wan to add you code to my personal.xls and use it permanently.
The result of: Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1)) is always Nothing in the vb local window-
Sorry, about my english level.
Hi Khushnood
I would very much appreciate you sharing the .bas module that you referred to 10.49 today.
Thanks
Gerald Strever
Khushnood, appreciate if you could post the bas module you referred to earlier. That would be a great tool to have!
Mike, great code. If you get the nobel, then MS should get the ignobel prize for not having a pivot table do this in the first place.
Oakhome - there was a piece of code at http://www.dailydoseofexcel.com/archives/2010/08/03/toggle-pivotfields-from-count-to-sum/ that you could adapt easily enough. Plus a streamlined bit of code in the comments that causes almost as much eyestrain as Mike's blindingly cool code above.
Here goes :
@ Jeff:
wonderful - thank you for sharing.. now to not think of all those times I've gone through the baluba of doing this the hard way, and rather enjoy the magic
Bonus: this is a useful VBA add-in -
Smart Indenter v3.5
http://www.oaltd.co.uk/indenter/default.htm
It works just fine on Office 2007.
Khushnood
Also one other thing :
The variable ' i ' is shown as not defined when you try to run the code (if you have Option Explicit specified).
You can add : "Dim i as Integer" in the code.
This should take care of things until Microsoft manages to cross the 32767 columns barrier in a spreadsheet ;-p
In any case, a table with fields/columns more than a fraction of this number would be better off in a Database application, right ?!
Khushnood
Khushnood: I added a declaration for i in the code. Thanks.
Sergio: Strange error you're getting. Could it be that yoiu are using an external data source for your pivot table? What do you see when you run this test:
Sub SourceTest()
Dim oPivotTable As PivotTable
Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
MsgBox oPivotTable.SourceData
End Sub
Hey Data¡¡ Thanks for answering so fast....but. I still have the same problem.. I used your the SourceTest code you have passed me, and the result its a msg with range of the data source of the pivot table ( that it is in the same workbook), the problem I think it´s not in that part of the code but the folowing line it´s where I got the error.
Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))
Here it´s where the code crash. It gives me an error, and then go to Myerr (logically). If I use de F8 to run the code step by step, it´s in this line where the code jumps to Myerr , it sets oSourceRange ="Nothing". It´s very strange, because if I get out this part of code and I put directly the range of the SourceTable of the pivot table, it´s runs.
Could I send you a e-mail with my excel??
Sergio:
Please send me your workbook. use mike@datapigtechnologies.com
I have been complaining for years about PivotTables not automatically using the source formatting. Once again, the solution comes not from Microsoft.
Bill Gates, while giving half his fortune away, should cough up a few thousand bucks for you.
As a thank you for your tips a link you might like:
http://www.onlineschools.org/blog/bacon/
Hi Mike,
I am Frank from Germany having exactly the same problem as Sergio
"The macro says me that I´m not in pivot table, when I actually I am, I review your code and I have a error on the folowing line:
Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))"
However, if I use a name for the SourceData, it works fine. Obviously, the conversion of the SourceData is problematic!? There seems to be no conversion to xlA1 in Excel 2010!?
Datapig,
I am surprised I didn't see your name on the Nobel last year for this bit of magical code!! It has helped me tremendously.
I am wondering though if you could help me figure out how to enhance it a bit to also bring over the font colors of the source data values?
Could you provide this for text formatting as well?