My pivot table created by a VBA Macro is not displaying the account numbers in full. It summaries them with a scientific notation.
What VBA code can I use in the macro to force it to fully display the number irrespective of which PC it's run? Code of Macro & sample data is shown in the tabs of the attached file.
When I run a corrected version (your code tries to create 2 pivot tables) of the macro I get the full 13 digits of the account numbers showing in the pivot. So perhaps a quick fix is not to try and change the formatting at all!
As soon as I try to adjust the formatting (to text) it then converts to scientific notation - which to me, is perverse.
I feel that the account numbers in your source data could need to be cells formatted as Text, rather than General or otherwise; this is because these account numbers should never be treated as numbers proper because you're never going to do any calculations on the 'numbers' - they're identifiers only - you're never going to add two account numbers together to get anything meaningful.
So I suggest that you make sure that the source data account numbers are text.
Ideally, the way the source data gets into your excel sheet in the first place needs to be such that they're text from the outset. That depends on how that data gets there; can you enlighten us on that?
Another possible fix is to to convert those account numbers to text, but it's not as straightforward as simply selecting the account numbers in the source data and altering the cells' formats.
One way is to select those account number cells in the source data, then using Text-to-columns in the Data Tools section of the Data tab of the ribbon, and in the first 2 steps ensuring that nothing tries to split your data, and in the 3rd step choosing Text as the column Data format, and clicking Finish. If you've been successful you should see the little green triangles in the top left of each cell notifying you that these cells contain numbers formatted as text.
Now try your code, still without trying to reformat the cells in the pivot table - I'm guessing it should be fine.
1. If you use On Error Resume Next, you really should IMO go back to normal error checking as soon as possible with On Error Goto 0
2. Easiest way is to fix the account number formatting in the data
3. Because you left Resume Next on for the rest of the macro, some error were just ignored
i.e.
Code:ActiveSheet.PivotFields("BANK NAME").Subtotals
4. It's easier for us to see if you just include the macro in a module in the workbook, instead of pasting the text into a worksheet
5. Try this workbook and see
Code:Option Explicit
Sub InsertPivotTable()
'Macro modified by Crabmanrocks from ExcelChamps original template. Macro to summarise payment data in reports/pivot table.
'Declare Variables
Dim i As Long
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'fix data
With PRange ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
For i = 2 To .Rows.Count
.Cells(i, 6).Value = "'" & .Cells(i, 6).Text
Next i
End With
'Define Pivot Cache
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=PRange, Version:=6).CreatePivotTable _
TableDestination:=PSheet.Cells(2, 2), TableName:="PaymentsPivotTable", DefaultVersion:=6
Set PTable = PSheet.PivotTables("PaymentsPivotTable")
With PTable ' <<<<<<<<<<<<<<<<<<<<<<<
'Insert Row Fields
With .PivotFields("Date Paid")
.Orientation = xlRowField
.Position = 1
End With
'Insert Row Fields
With .PivotFields("CLAIMANT/BENEFICIARY")
.Orientation = xlRowField
.Position = 2
End With
'Insert Row Fields
With .PivotFields("ACCOUNT NO:")
.Orientation = xlRowField
.Position = 3
End With
'Insert Column Fields
With .PivotFields("BANK NAME")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("AMOUNT")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Payments "
End With
'Format Pivot Table
.ShowTableStyleRowStripes = True
.TableStyle2 = "PivotStyleMedium9"
' With ActiveSheet.PivotFields("BANK NAME").Subtotals(1) = False ' <<<<<<<<<<<<<
.PivotFields("BANK NAME").Subtotals(1) = False
' .PivotFields("ACCOUNT NO:").NumberFormat = "0_;-0" ' <<<<<<<<<<<<<<<<<<
' .PivotFields("ACCOUNT NO:").NumberFormat = "@" ' <<<<<<<<<<<<<<<<<<
End With
End Sub
Thanks P45CAL for your answer. In the end, I had to use the CONCATENATE function in the data sheet to add a space before the account number. This forced the pivot table to show the full number as opposed to the scientific notation. In addition, it was best i broke down the code into smaller modules. This helped to easily identify any errors in the code. Many thanks for your help. I am newbie to VBA and loving the support from you and the community.
Thanks Paul for your answer. In the end, I had to use the CONCATENATE function in the data sheet to add a space before the account number. This forced the pivot table to show the full number as opposed to the scientific notation. In addition, it was best i broke down the code into smaller modules. This helped to easily identify any errors in the code. Many thanks for your help. I am a newbie to VBA and loving the support from you and the community.
Adding the space in front tells Excel that "This is text, so leave it alone"
The same result if you add a single quote in front
Code:.Cells(i, 6).Value = "'" & .Cells(i, 6).Text