You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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
'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
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.
I pasted the code in the editor for the sheet and ran it
It is showing error "Compile error: syntax error"
also "Set oSourceRange = " is in red color
how to fix this error?
The below should be one single line. The HTML code of the web page is warping it to 2 lines.
"Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))"
Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))
Also please remove the Quotes.
Thanks for the fix. It is solved.
I have placed the cursor in a cell of the pivot table. and ran the code. Still it is displaying the msg "You must place your cursor inside of a pivot table".
is there any way to clear the last active cell position?
Everytime I run it I keep getting the "Be sure you start with your cursor inside a pivot table" message, despite the cursor being in a Pivot field - so can't comment further on the code - what's the problem ? Do you mean that the cursor must be in a cell within the Pivot Table - in which case it doesn't work ?
Mick: Yes. The active cell must be inside the pivot table. The code will work only if the pivot table's source data is in the local workbook. So if the source data has been deleted, or if the source data is external, then the code will will not work.
This is brilliant. Thanks so much!
I'm going to browse some more, but one question I have regarding pivot tables: In Excel 2007, which I use, it is to my knowledge impossible to change the default pivot table layout and subtotal settings. I wish I could set the defaults to "Do not show subtotals" and Report Layout to "Show in Tabular Form." This would be a huge timesaver, as I use pivot tables and database formulas such as =vlookup quite often to marry data found in different tables.
@ Joe L
In Excel 2007 you can turn off subtotals:
PivotTable Tools-> Design-> Subtotals
We also added this nifty feature in Excel 2010 called "Repeat Item Labels" so that you can easily use vlookups on all your Pivot data.
PivotTable Tools -> Design -> Repeat all Item Labels.
Can you please tell me where to paste the code?