Applying "PivotField.Autosort" when generating a pivot chart

Question

I am creating a macro to generate some clustered column pivot charts. I have used examples from the internet/stack overflow to generate the pivot table, generate the pivot chart and display the columns in decreasing order.

I have consulted the Microsoft Documentation and tried fiddling with the syntax of PivotField.Autosort.

Here is a generalised version of my code with comments. The Autosort command to display the columns in descending order is the last two lines:

'Declare Variables
Dim PSheet As Worksheet 'To create a sheet for a new pivot table.
Dim DSheet As Worksheet 'To use as a data sheet.
Dim PCache As PivotCache 'To use as a name for pivot table cache.
Dim PTable As PivotTable 'To use as a name for our pivot table.
Dim PRange As Range 'to define source data range.
Dim LastRow As Long 'To get the last row and column of our data range.
Dim LastCol As Long '

Dim chtObj      As ChartObject
Dim PvtSht      As Worksheet
Dim PvtTbl      As PivotTable

'After inserting a new worksheet, this code will set the value of FSheet
'variable to pivot table worksheet and DSheet to source data worksheet.
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("MaintActivType").Delete
Worksheets("Sheet1").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "MaintActivType"
Application.DisplayAlerts = True
Set PSheet = Worksheets("MaintActivType")
Set DSheet = Worksheets("Sheet1")
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
'In Excel 2000 and above, before creating a pivot
'table you need to create a pivot cache to define the data source.
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="MaintenanceData")

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

'Insert Row Fields
With ActiveSheet.PivotTables("MaintenanceData").PivotFields("MaintActivType")
    .Orientation = xlRowField
    .Position = 1 'this field allows for sub-categories of data to be displayed
End With

'Insert Column Fields
With ActiveSheet.PivotTables("MaintenanceData").PivotFields("MaintActivType")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .Name = "Count of MaintActivType"
End With

' set the Pivot sheet
Set PvtSht = Sheets("MaintActivType")

' set the Pivot Table object
Set PvtTbl = PvtSht.PivotTables("MaintenanceData")

' set the Chart Object
Set chtObj = PvtSht.ChartObjects.Add(300, 200, 550, 200)

' modify ChartObject properties
With chtObj
    .Chart.SetSourceData PvtTbl.TableRange2 ' set the chart's data range to the Pivot-Table's TableRange2
    .Chart.ChartType = xlColumnClustered
    .Name = "Maintenance Activity Type"
    .Chart.HasTitle = True
    .Chart.ChartTitle.Text = "Maintenance Activity Type"
    .Chart.HasLegend = False
End With

ActiveSheet.PivotTables("MaintenanceData").PivotField("MaintActivType") _
  .AutoSort xlDescending, "Sum of MaintActivType"
'End With

I don't get any error messages associated with the Autosort command, but if I comment out the On Error Resume Next I get a Type mismatch error for the pivot cache creation ("Set Pcache ..."), which is baffling because that part works just fine with the error message suppressed.

score 0 · Answer 1 · answered Oct 23 '19 at 05:02

0

"that part works just fine with the error message suppressed" it doesn't work fine, it just doesn't matter that it doesn't work.

Dim PCache As PivotCache

'...
'...

Set PCache = ActiveWorkbook.PivotCaches.Create _
          (SourceType:=xlDatabase, SourceData:=PRange). _
          CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
          TableName:="MaintenanceData")

Here you create a pivotcache, but then use it to create a pivottable, and try to assign that pivottable to your PCache variable (which can only be used for a pivotcache...) So that raises a run-time error, but by that point the pivottable is created, and you pick that up on the next line.

VBA Copy Pivot Table to New Sheet

answered Oct 23 '19 at 05:02

Tim Williams

154,628
8
97
125

Thanks for your reply, I looked at your previous example and that cleared up some errors. I got the graph sorted in the way I wanted with the attached code; is there a better way of doing this? (sorry for the shitty formatting) `` With PvtSht .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Sort _ Key1:=.Range("B2"), Order1:=xlDescending, _ Header:=xlYes End With `` – snappy_irides Oct 23 '19 at 21:38
That looks fine to me – Tim Williams Oct 23 '19 at 21:57

Applying "PivotField.Autosort" when generating a pivot chart

1 Answers1