CODEWITHSUNDEEP
vbaexcelpivot-table
Pericles Faliagas
Pericles Faliagas

Reputation: 636

VBA code for creating Pivot Table

I have created a macro that builds a pivot table. It runs great but I want to dismiss the circled content in the screenshot provided .. I dont want to have the total inside the pivot. Only at the bottom the Grand Total! I am providing my code and a screenshot. Moreover, is it possible to substitute the "Row Labels" text in column B with "FDD"?

enter image description here

enter image description here

     Sub aa()

 'Declare Variables
    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long

    'Insert a New Blank Worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Customs_report").Delete
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Customs_report"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("Customs_report")
    Set DSheet = Worksheets("Zeikan")

    'Define Data Range
    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(6, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(6, 1).Resize(LastRow, LastCol)

    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
    TableName:="PivotTable")

    'Insert Blank Pivot Table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")

    'Insert Row Fields
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Commodity Code")
    .Orientation = xlRowField
    .Position = 1
    .Name = "Commodity Code"
    End With
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Location")
    .Orientation = xlRowField
    .Position = 2
    End With

    'Insert Column Fields
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Quantity (cases/sw)")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlSum
    .Name = "Sum of Quantity (cases/sw)"
    End With
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Quantity (items)")
    .Orientation = xlDataField
    .Position = 2
    .Function = xlSum
    .Name = "Sum of Quantity (items)"
    End With
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Net value")
    .Orientation = xlDataField
    .Position = 3
    .Function = xlSum
    .Name = "Sum of Net value"
    End With
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Gross weight (Kg)")
    .Orientation = xlDataField
    .Position = 4
    .Function = xlSum
    .Name = "Sum of Gross weight (Kg)"
    End With
    With ActiveSheet.PivotTables("PivotTable").PivotFields("Net weight (Kg)")
    .Orientation = xlDataField
    .Position = 5
    .Function = xlSum
    .Name = "Sum of Net weight (Kg)"
    End With


    End Sub

Upvotes: 1

Views: 2931

Answers (1)

Rory
Rory

Reputation: 34075

You can toggle the subtotals:

With PTable.PivotFields("Commodity Code")
.Orientation = xlRowField
.Position = 1
.Name = "Commodity Code"
.Subtotals(1) = True
.Subtotals(1) = False
End With

to change the header, change the table layout to tabular

PTable.LayoutRowDefault = xlTabularRow

before you add the fields. BTW, you should be using PTable rather than ActiveSheet.PivotTables("PivotTable") in the rest of your code.

Upvotes: 4

Related Questions