Re-organizing pivot table fields in vba

[ummjay]

Hi! how can I reorganize the order of the pivot fields: Client Type, Product, Side. When I run the below, the rows are in the following order: side, product, client type:

'START pivot
 ActiveSheet.UsedRange.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets(1).UsedRange).CreatePivotTable TableDestination:="", _
        TableName:="Pivot Summary", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
'Insert Side to Row Filed & position 2
With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Client Type")
        .Orientation = xlRowField
        .Position = 1
    End With
  'Insert Side to Row Filed & position 2
With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Product")
        .Orientation = xlRowField
        .Position = 1
    End With
  'Insert Side to Row Filed & position 2
  With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Side")
    .Orientation = xlRowField
    .Position = 1
    End With
'Insert Qty column to the data field
  With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Volume")
    .Orientation = xlDataField
    .Position = 1
  .NumberFormat = "#,##0;(#,##0)"
  
  ActiveWorkbook.RefreshAll
    'Turn on Automatic updates/calculations --like screenupdating to speed up code
    pvt.ManualUpdate = False
  
  End With
  
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[DanteAmor]

Hi @ummjay. Thanks for posting on the board.

Just update the position number:

  'Insert Side to Row Filed & position 1
  With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Client Type")
    .Orientation = xlRowField
    .Position = 1
  End With
  'Insert Side to Row Filed & position 2
  With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Product")
    .Orientation = xlRowField
    .Position = 2
  End With
  'Insert Side to Row Filed & position 3
  With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Side")
    .Orientation = xlRowField
    .Position = 3
  End With

The previous lines can be simplified like this

  With ActiveSheet.PivotTables("Pivot Summary")
    .PivotFields("Client Type").Orientation = xlRowField
    .PivotFields("Client Type").Position = 1
    .PivotFields("Product").Orientation = xlRowField
    .PivotFields("Product").Position = 2
    .PivotFields("Side").Orientation = xlRowField
    .PivotFields("Side").Position = 3
  End With

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[ummjay]

worked like a charm, thanks!