VBA - Move PivotItem to last position in sequence

[florianvk]

Goodday all,

I am trying to move a PivotItem to the last position in my Pivot table and found on the web below VBA code, but it doesn't work .
Does anybody have an idea why?

Dim total As Integer
total = ActiveSheet.PivotTables("PivotTable1").PivotFields("Vehicle Type Code").PivotItems.Count
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vehicle Type Code").PivotItems("TRUCK").Position = total

 

[Domenic]

Try declaring your variable as Long, instead of Integer. Also, your code could be re-written as follows...

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vehicle Type Code")
    .PivotItems("TRUCK").Position = .PivotItems.Count
End With

Hope this helps!

 

[florianvk]

Hi Domenic,

Thanks for your reply, but unfortunayely still giving error.
Will try it on another way. My table (loadlist) has normally 4 different Vehicle Type Codes: CAR, SVAN, VAN & TRUCK.
I want to have my pivot table always showing the same with the Vehicle Type Codes (VTC) in above sequence.
Some loadlists however, doesn't have some VTC.
I want to add in that case a Pivot Item which doesn't exist in the list, but showing just blank in the Pivot Table.
I will have then always 4 items and can move "TRUCK" always to position 4.

But how to add an PivotItem in the Pivot Table which does not exist in the data?

Hope you can help me out.

 

[Domenic]

Some loadlists however, doesn't have some VTC.

First you'll need to set the number of items to retain to "None"...

Right-click the pivot table > PivotTable Options >  Data > Number of items to retain per field > None

Then try...

[COLOR=darkblue]With[/COLOR] ActiveSheet.PivotTables("PivotTable1")
    .RefreshTable
    [COLOR=darkblue]With[/COLOR] .PivotFields("Vehicle Type Code")
        .PivotItems("TRUCK").Position = .PivotItems.Count
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] With

I want to have my pivot table always showing the same with the Vehicle Type Codes (VTC) in above sequence.

Try...

    [COLOR=darkblue]Dim[/COLOR] vVehicleTypes [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vVehicleType [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oPvtItm [COLOR=darkblue]As[/COLOR] PivotItem
    [COLOR=darkblue]Dim[/COLOR] PvtCnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    vVehicleTypes = Array("CAR", "SVAN", "VAN", "TRUCK")
    
    PvtCnt = 0
    [COLOR=darkblue]With[/COLOR] ActiveSheet.PivotTables("PivotTable1")
        .RefreshTable
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
        [COLOR=darkblue]With[/COLOR] .PivotFields("Vehicle Type Code")
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] vVehicleType [COLOR=darkblue]In[/COLOR] vVehicleTypes
                [COLOR=darkblue]Set[/COLOR] oPvtItm = .PivotItems(vVehicleType)
                [COLOR=darkblue]If[/COLOR] Err = 0 [COLOR=darkblue]Then[/COLOR]
                    PvtCnt = PvtCnt + 1
                    .PivotItems(vVehicleType).Position = PvtCnt
                [COLOR=darkblue]Else[/COLOR]
                    Err.Clear
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] vVehicleType
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!

 

[florianvk]

Hi Domenic,

Macro is not giving any error anymore and placing TRUCK in last position. So thanks for that already.
Furthermore it should be nice if my pivot table always have the same layout by always showing CAR, SVAN, VAN and TRUCK as Vehicle Type Code even if one of them is not existing in the data list
So, if a loadlist is for example not containing any cars the pivot table should still show the Vehicle Type code CAR, but just leaving the value field blank.

The reason for this is that my pivot table is showing for each Vehicle Type the number, Linear Meter (total of Lenght), Average Square Meter and Average Weight. For Trucks I want to hide the average square meter, but for the other 3 types I want to hide the Linear meter. If all Vehicle types are shown and have their fixed position I can hide those columns by setting the columnwidth of them to zero.
I hope you follow a bit what are my intentions.

Does it work if you change in above code Dim vVehicleTypes As Variable by Dim vVehicleTypes As Integer or something?