using vba to sort update pivot on changing pivot field name

Question

I have a pivottable consisting of 43 pivotfields. 39 om them are used for the datafields. Every 4 weeks, the data is updated with new data where the first of those 39 datafields is no longer in the data and at the end a new data field was added.

I have a script which deletes all the data fields and them add all the 39 data fields that are needed. The only thing that is left is sorting the data after that by using the (values of the) last data field.

I thought that I could get the name of the last data field and use that as a string for the sort script and I would be done. But it did not turn out as I hoped it would.

I have this for readin/getting the pivot field value which I want to use for sorting:

Sub AsSum()
Dim pf As PivotField
Dim pfvalue As String

With Worksheets("pivot").PivotTables("Draaitabel1")
pfvalue = .PivotFields(42).Name


.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlSum
.Caption = "." & .SourceName
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With

Worksheets("pivot").PivotTables("Draaitabel1").PivotCache.Refresh

Worksheets("pivot").PivotTables("Draaitabel1").PivotField("Product") _
 .AutoSort xlDescending, pfvalue

End Sub

What am I doing wrong here to get my table sorted by the last added data period?

Below is the some part of the layout of the pivot and the list of pivot fields available: I want to sort the pivot on the arrowed pivot column/field

the pfvalue script perfectly reads out the correct field name:

but when I get down to the actual sorting, I get an error (error 438 during execution: this property or method is not supported by this object):

with this being the actuel pivot layout/design:

When a start the macro recorder and I just manually sort the pivot by this last column, this is the script the macro recorder creates:

ActiveSheet.PivotTables("Draaitabel1").PivotFields("Product").AutoSort _
        xlDescending, ".Qw 32, 2018", ActiveSheet.PivotTables("Draaitabel1"). _
        PivotColumnAxis.PivotLines(39), 1

I tried to use this and add the last part to my script to see if it makes a difference, but it does not, this is how I added it to my script:

Worksheets("pivot").PivotTables("Draaitabel1").PivotCache.Refresh

Worksheets("pivot").PivotTables("Draaitabel1").PivotField("Product") _
 .AutoSort xlDescending, pfvalue, Worksheets("pivot").PivotColumnAxis.PivotLines(39), 1


End Sub

Still those last lines for the actual sorting are highlighted yellow by the vba debugging.

"I get an error" What error exactly? Also please don't post code as images, just copy paste the code itself, that makes it a lot easier for someone to recreate your code to debug it. — Luuklag
I missed the error number, I thought I had none. I've added the error message in my post. The images with code are just to illustrate the values the code returns, the actual code is in my question posted as code. — DutchArjo
Have a look at your With line and the first line that errors out. Now read those as they were one line, and you might see what is wrong ;) — Luuklag
I have spend much time looking at the lines you mentioned but I just don't see it.. — DutchArjo
That is because you changed your code. In the previous version it read: Worksheets().pivottable().pivottable() — Luuklag

DutchArjo DutchArjo · Accepted Answer · 2018-09-21T14:15:06

I found the problem.

this line below misses only one letter:

Worksheets("pivot").PivotTables("Draaitabel1").PivotField("Product") _
 .AutoSort xlDescending, pfvalue

Instead it had to be: Worksheets("pivot").PivotTables("Draaitabel1").PivotFields("Product") _ .AutoSort xlDescending, pfvalue

Now it sorts perfectly.

using vba to sort update pivot on changing pivot field name

1 Answers