asked on

How to add another field to a pivot table using vba

Hello,

I am working on pre-existing code. I was asked to add an additional field to the pivot table called "Reason_Code". I added it and it appears in the Pivot Table Field List but needs to be checked in order for it to appear in the pivot table itself

 Set PivotDataRange = rng
    Set PTCache = wb.PivotCaches.Create _
        (SourceType:=xlDatabase, _
        SourceData:=PivotDataRange, _
        Version:=xlPivotTableVersion12)
    Set sPT = PTCache.CreatePivotTable(TableDestination:=wsPiv.Cells(4, 1), TableName:="ScrapData", DefaultVersion:=xlPivotTableVersion12)
    
    With sPT.PivotFields( _
        "User_Transaction_Entered")
        .Orientation = xlPageField
        .Position = 1
    End With
    With sPT.PivotFields("Warehouse")
        .Orientation = xlPageField
        .Position = 1
    End With
    With sPT.PivotFields("Transaction_Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With sPT.PivotFields("Item_Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    With sPT.PivotFields("Item_Description")
        .Orientation = xlRowField
        .Position = 2
    End With

 With sPT.PivotFields("Reason_Code")
        .Orientation = xlRowField
        .Position = 3   
 End With

    sPT.AddDataField sPT.PivotFields("Qty"), "Quantity", xlSum
    sPT.AddDataField sPT.PivotFields("SumOfAmount"), "Amount", xlSum
    
    'Change number VBA.Format
    With sPT.PivotFields("Amount")
        .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    End With
    
    With sPT.PivotFields("Quantity")
        .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    End With
    
    'Sort largest to smallest
    sPT.PivotFields("Item_Code").AutoSort _
        xlDescending, "Amount", sPT.PivotColumnAxis.PivotLines(2), 1
    
    'Remove grand totals for rows
    sPT.RowGrand = False
        
    'Remove subtotals for each item
    sPT.PivotFields("Item_Code").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    '******************************************
       
    sPT.TableStyle2 = "PivotStyleLight16"
    
    With wsPiv.Cells.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    
    'Change display to Classic pivot table style
    With sPT
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
                    
    'Activate pivot table sheet & save workbook
    wsPiv.Activate
    wsPiv.Range("B7").Select
    wb.Save
    wb.Close

Open in new window

Juan Velasquez

ASKER

When I copy the database, from which the excel pivot table is created (via automation), to my hard drive. And run the code from the local database, everything works fine. The reason_code appears in the Pivot Table Fields List, and is checked and appears as a row in the Pivot Table. It must be something in my installation.

Professor J

can u portray here how do you want the Reason Code to be appeared? i am not sure if i understood your question.

ASKER CERTIFIED SOLUTION

Juan Velasquez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juan Velasquez

ASKER

It was my mistake