VBA: Sorting Pivot Tables with VBA

[dan_l]

It seems like every question I ask has something to do with VBA. I have no idea why.


I have this:


Sheets("groups").PivotTables("PT1").PivotFields("Name").AutoSort _

xlDescending, "Average of W", ActiveSheet.PivotTables("PT1"). _

PivotColumnAxis.PivotLines(1), 1


It works when I'm actually on the groups worksheet. However, when I'm on a different worksheet, excel throws an error.

 

[Luke M]

Sheets("groups").PivotTables("PT1").PivotFields("Name").AutoSort _

xlDescending, "Average of W", ActiveSheet.PivotTables("PT1"). _

PivotColumnAxis.PivotLines(1), 1


Change the bold bit. =)

 

[dan_l]

I fail.

 

[Hui]

Sheets("groups").PivotTables("PT1").PivotFields("Name").AutoSort _

xlDescending, "Average of W", Sheets("groups").PivotTables("PT1"). _

PivotColumnAxis.PivotLines(1), 1

 

[dan_l]

Weird. It works, but only sometimes. Sometimes it works as written - other times it just does nothing.

 

[dan_l]

Ok I actually made it work:


Sheets("groups").PivotTables("PT1").PivotFields("Name").AutoSort _

xlDescending, "Average of W", Sheets("groups").PivotTables("PT1"). _

PivotColumnAxis.PivotLines(1), 1


the last part....PivotLines(1) is actually a variable that's kind of an index against your pivot table.


Ok, one more silly question:


I want to pass the xlDescending argument as a variable. I've tried passing it as cells(x,y), I've tried to dim a string and pass the contents of cell x,y........but it doesn't seem to work.


Is there a way to achieve this?

 

[Hui]

I often just use a simple


Dim sOrder

sOrder = xlAscending

or

sOrder = xlDescending


then


ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _

SortOn:=xlSortOnValues, Order:=sOrder, DataOption:= _

xlSortTextAsNumbers


Note: That sOrder is dimensioned as a variant not a string.