Showing Repeated Rows With a Repeated Quantities in Excel Pivot Table via VBA

Question

I have a Bill of Material (BOM) table which have repeated items for different functions. I have a VBA codes to automatically create pivot table of this one to see what is the total value of Funtion column.

             A                      B                C             D     

         Description             Quantity         Cost($)       Function

  1      Pr. Vessel_A               1               320         Pre Treat
  2      Pr. Vessel_A               1               320         Post Treat
  3      Pr. Vessel_A               1               320         Primary RO
  4      Pr. Vessel_A               1               320         Pre Treat
  5      Pr. Vessel_A               20              6400        Secondary RO
  6      Membrane_00B               5               505         Pre Treat     
  7      Membrane_00B               5               505         Primary RO
  .
  .
  .

So what I want to see is:

          Function               Quantity          Cost($)      Description

  1      Pre Treat                  1               320         Pr. Vessel_A
  2      Pre Treat                  1               320         Pr. Vessel_A
  3      Pre Treat                  5               505         Membrane_00B
  4      Total Pre Treat                            1145        

  5      Primary RO                 5               505         Membrane_00B
  6      Primary RO                 20              6400        Pr. Vessel_A
  7      Total Primary RO                           6905       

  8      Post Treat                 1               320         Pr. Vessel_A
  9      Total Post Treat                           320  

  10     Secondary RO               1               320         Pr. Vessel_A
  11     Total Secondary RO                         320     
  .
  .
  .

I was using this code for a long time but today I realized that, if the Item description and the quantity are same in the same function, then pivot table only shows me one of them.

And in this example, I only see 1 quantity Pr. Vessel_A of the Pre Treat function but cost for 2 quantity. So ok, the total cost is correct but quantities are not. Instead of this I need to see two different lines to also see the quantities correctly as well.

This is what I see now:

          Function               Quantity          Cost($)      Description

  1      Pre Treat                  1               640         Pr. Vessel_A
  2      Pre Treat                  5               505         Membrane_00B
  3      Total Pre Treat                            1145

I've been trying to correct this by myself but wasn't able to do this. Also didn't find exact solution online. So how should I edit my code to also see the repeated description rows with a repeated quantities in my pivot table? This is my code:

Sub CreateProcesSectList()
     'Create worksheet "ProcessSectionsList" if it doesn't exist.  And then Create a Process Sections Pivot Table
     Dim Baslik1, Baslik2, Baslik3, Baslik4, Baslik5 As String

     Baslik1 = Sheet5.Range("F2").Value  'Items' Descriptions
     Baslik2 = Sheet5.Range("H2").Value  'Process Sections
     Baslik3 = Sheet5.Range("I2").Value  'Quantity
     Baslik4 = Sheet5.Range("K2").Value  'Unit cost
     Baslik5 = Sheet5.Range("M2").Value  'Total Cost


     Application.ScreenUpdating = False

    CreateSheetIf ("ProcessSectionsList")

    Sheets("ProcessSectionsList").Select
    Columns("A:AK").Select
    Range("A1").Activate
    Selection.Delete Shift:=xlToLeft
    Sheet5.Select
    Application.Goto Reference:="R100000C6"
    Selection.End(xlUp).Select
    ActiveCell.CurrentRegion.Select
    Set tblo = ActiveCell.CurrentRegion
tblo.Offset(1, 0).Resize(tblo.Rows.Count - 1, _
 tblo.Columns.Count).Select

     'Sheet5.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "R2C2:R388C22", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="ProcessSectionsList!R1:R1048576", TableName:="ProcessSectionsPivotTable", _
        DefaultVersion:=xlPivotTableVersion14  

   Range("a1").Select

    Sheets("ProcessSectionsList").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik2)
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik1)
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik3)
        .Orientation = xlRowField
        .Position = 3
    End With
     With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik4)
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("ProcessSectionsPivotTable").AddDataField ActiveSheet.PivotTables( _
        "ProcessSectionsPivotTable").PivotFields(Baslik5), "Count of Total Cost", xlCount
    With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik2)
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
    ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik1). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik1)
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
      With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik3)
        .LayoutForm = xlTabular
        .RepeatLabels = True
        .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    End With
      With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik4)
        .LayoutForm = xlTabular
        .RepeatLabels = True
        .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
        End With
    Range("C223").Select
    ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields("Count of Total Cost"). _
        Function = xlSum
    Range("A1").Select
    With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik2)
        .PivotFilters.Add Type:=xlCaptionDoesNotBeginWith, Value1:="0"
        .PivotItems("(blank)").Visible = False
    End With

  With ActiveSheet.PivotTables("ProcessSectionsPivotTable")
  .TableStyle2 = "PivotStyleLight20"
  .ShowDrillIndicators = False

  End With

    Range("a1").Select
    ActiveSheet.PivotTables("ProcessSectionsPivotTable").CompactLayoutRowHeader = Baslik2
    Columns("d:e").Select
    Selection.Style = "Currency"
    Columns("B:B").ColumnWidth = 54.14
    Range("a1").Select
   ActiveWindow.ScrollRow = 1

     Application.ScreenUpdating = True
End Sub

As long as I have ".RepeatLabels = True" for Baslik1 and Baslik3 (Quantity and Descriptions) what is missing?

@MarkFitzgerald yes, but the issue is not with that sub, CreateSheetIf is only creating a ProcessSectionsList sheet if it doesn't exist. — Mertinc
I think you will need something to distinguish the duplicated data rows - possibly another column "Unique ID" with sequential numbers for each data row. — Mark Fitzgerald
@MarkFitzgerald , when I Record Macro and do these steps manually to get the code, it exactly seems similar, so there is no need to add another column. I miss something within this code but couldn't find it yet. — Mertinc
@MarkFitzgerald I've just edited the question. Could you please check it. I realized that my row number was not correct that's why the sum was different. But after I corrected the row number, now same issue is continuing except I see the correct SUM. So my Pivot Table recognizes repeated rows and counts them, but doesn't show all of the rows and shows only one of them's quantity. — Mertinc

Mertinc Mertinc · Accepted Answer · 2017-05-18T17:51:33

I solved this issue by adding another data column to the table.

In this case my table needs to repeat all of my items as long as the values in their new added column are different.

Showing Repeated Rows With a Repeated Quantities in Excel Pivot Table via VBA

1 Answers