VBA - Move PivotItem to last position in sequence

florianvk

New Member
Joined
Apr 26, 2016
Messages
8
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 :mad:.
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,165
Try declaring your variable as Long, instead of Integer. Also, your code could be re-written as follows...

Code:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vehicle Type Code")
    .PivotItems("TRUCK").Position = .PivotItems.Count
End With
Hope this helps!
 

florianvk

New Member
Joined
Apr 26, 2016
Messages
8
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,165
Some loadlists however, doesn't have some VTC.
First you'll need to set the number of items to retain to "None"...

Code:
Right-click the pivot table > PivotTable Options >  Data > Number of items to retain per field > None
Then try...

Code:
[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...

Code:
    [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

New Member
Joined
Apr 26, 2016
Messages
8
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,029
Messages
5,466,116
Members
406,467
Latest member
bpat83

This Week's Hot Topics

Top