Sorting on Pivot Table Calculated column

DAOsland

New Member
Joined
Jul 27, 2018
Messages
3
I have a pivot table based on a SQL query of transactions. the pivot table summarizes sales activity by unit producing a two year history by item and customer. There are three calculated columns that are repeated for each month and then a yearly set. Of interest here is the current year total units.

I figured the simplest way to do the sort would be to right click column and sort. This does nothing in the YTD column. So I recorded a Macro of YTD and Dec 2018 shown below:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("BZ15").Select
ActiveSheet.PivotTables("Select_Unit_Sales").PivotFields("Item").AutoSort _
xlDescending, "sales ", ActiveSheet.PivotTables("Select_Unit_Sales"). _
PivotColumnAxis.PivotLines(76), 1
End Sub
Sub Macro2()
'
' Macro2 Macro
'


'
Range("BW15").Select
ActiveSheet.PivotTables("Select_Unit_Sales").PivotFields("Item").AutoSort _
xlDescending, "sales ", ActiveSheet.PivotTables("Select_Unit_Sales"). _
PivotColumnAxis.PivotLines(73), 1
End Sub

Column 76 is the YTD, Column 73 is the Dec 2018 amount.

These are all effectively calculated fields but YTD is a horizontal total (months 1 - 12), each month is a sum of the customers, which may in turn be a sum of many transaction records.

Here is the VBA definition of the fields:

With PT.PivotFields("Itemname") ' which item
.Orientation = xlRowField
.name = "Item"
End With

With PT.PivotFields("FullName") ' full name of branch
.Orientation = xlRowField
.name = "Branch"
End With





With PT.PivotFields("sales") ' sales $
.Orientation = xlDataField
.Function = xlsumm
.NumberFormat = "#,###;(#,###)[Red]" ' format number - no decimal red if negative
.name = "sales "

End With


With PT.PivotFields("CalYear") ' year
.Orientation = xlColumnField
.name = "Year"
End With


With PT.PivotFields("CalMnth") ' spread by month
.Orientation = xlColumnField
.Position = 2
.name = "Mnth"
End With


Does anybody have any ideas?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top