Pivot table Code Troubleshooting

Closed
x.dominicraj Posts 7 Registration date Thursday May 18, 2017 Status Member Last seen June 1, 2017 - Jun 1, 2017 at 10:25 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jun 6, 2017 at 11:23 AM
Hello,



I am trying to create an auto pivot table using some data that I have. However after repeated attempts at troubleshooting, I am unable to understand as to why the code wont run. Below is the code:

Sub InsertPivotTable()

'Declare Variables
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
Dim I As Integer

'Delete Previous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
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)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="SalesPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

With ActiveSheet.PivotTables("PivotTable").PivotFields("FISCPER")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("CUSTOMER")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("FISCYR")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("DESC")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
"PivotTable").PivotFields("AMTFUNC"), "Sum of AMTFUNC", xlSum

End Sub


Please help. Thank you in advance.

Best,
Dominic

2 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 1, 2017 at 11:38 AM
Hi Dominic,

When you use the line:
On Error Resume Next

Errors won't show, but they are still there. Remove that line to see where the code get stuck and go from there.

Best regards,
Trowa
0
x.dominicraj Posts 7 Registration date Thursday May 18, 2017 Status Member Last seen June 1, 2017
Jun 1, 2017 at 01:47 PM
Hi Trowa,

Thank you for your prompt response. When I remove that and ran the code, it shows a type 13 error : run-time mismatch. I thought its because of assigning LastRow As Long and LastCol As Long . However then it throws a different error so I am guessing its not the error. Any advise?

Best,
Dominic
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 6, 2017 at 11:23 AM
Hi Dominic,

On which code line does the code get stuck?

Best regards,
Trowa
0