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.
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
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