Loop through the pivot table and printing the available columns in current workbook

[GirishDhruva]

Hi Everyone ,
I created a pivot table using macro recorder for this month but for next month arrear-basic is not their in the workbook means error occurs, so any solution for that

With ActiveSheet.PivotTables("PivotTable6").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable6").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Cost Center")
.Orientation = xlRowField
.Position = 1
End With
'error begin'
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Arrears-Basic"), "Sum of Arrears-Basic", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Basic"), "Sum of Basic", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Arrears-HouseRentAllowance"), "Sum of Arrears-HouseRentAllowance", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("HouseRentAllowance"), "Sum of HouseRentAllowance", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("ChildrenEducationAllowance"), "Sum of ChildrenEducationAllowance", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Arrears-Other Allowance"), "Sum of Arrears-Other Allowance", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Other Allowance"), "Sum of Other Allowance", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Other Earnings"), "Sum of Other Earnings", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Other Earnings Non Taxable"), "Sum of Other Earnings Non Taxable", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Arrears-NPS EMPLOYER EARNING 80CCD2"), "Sum of Arrears-NPS EMPLOYER EARNING 80CCD2", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("NPS EMPLOYER EARNING 80CCD2"), "Sum of NPS EMPLOYER EARNING 80CCD2", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("ProvidentFund"), "Sum of ProvidentFund", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("ProfessionalTax"), "Sum of ProfessionalTax", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Employees StateInsurance"), "Sum of Employees StateInsurance", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Lunch Deduction"), "Sum of Lunch Deduction", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("IncomeTax"), "Sum of IncomeTax", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Parental Medical Deduction"), "Sum of Parental Medical Deduction", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("NPS EMPLOYER DEDUCTION80CCD2"), "Sum of NPS EMPLOYER DEDUCTION80CCD2", xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables("PivotTable6").PivotFields("Net Payment"), "Sum of Net Payment", xlSum
'error end'
ActiveWorkbook.ShowPivotTableFieldList = False

i need to loop over all the fields and print only available columns

 

[GirishDhruva]

Can anyone suggest me with the solution......ASAP
Thank you in prior

 

[DanteAmor]

Use this.
On the red line enter the names of the fields that should not be presented as a sum.

Sub test()
    Dim wField As PivotField
    
    With ActiveSheet.PivotTables("PivotTable6").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable6").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Cost Center")
        .Orientation = xlRowField
        .Position = 1
    End With
    
[COLOR=#0000ff]    With ActiveSheet.PivotTables("PivotTable6")[/COLOR]
[COLOR=#0000ff]        For Each wField In .PivotFields[/COLOR]
[COLOR=#0000ff]            Select Case wField.Name[/COLOR]
[COLOR=#ff0000]                Case "Cost Center", "Module"[/COLOR]
[COLOR=#0000ff]                Case Else: .AddDataField .PivotFields(wField.Name), "Sum of " & wField.Name, xlSum[/COLOR]
[COLOR=#0000ff]            End Select[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
End Sub

 

[GirishDhruva]

Use this.
On the red line enter the names of the fields that should not be presented as a sum.

Sub test()
    Dim wField As PivotField
    
    With ActiveSheet.PivotTables("PivotTable6").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable6").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Cost Center")
        .Orientation = xlRowField
        .Position = 1
    End With
    
[COLOR=#0000ff]    With ActiveSheet.PivotTables("PivotTable6")[/COLOR]
[COLOR=#0000ff]        For Each wField In .PivotFields[/COLOR]
[COLOR=#0000ff]            Select Case wField.Name[/COLOR]
[COLOR=#ff0000]                Case "Cost Center", "Module"[/COLOR]
[COLOR=#0000ff]                Case Else: .AddDataField .PivotFields(wField.Name), "Sum of " & wField.Name, xlSum[/COLOR]
[COLOR=#0000ff]            End Select[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
End Sub

Thank you so much sir. It worked exactly how i needed

 

[GirishDhruva]

Hi sir,
I need to find particular columns from pivot table and sum all those columns,(Like if i need to find the sum of only arrears,HRA,PT,PF) and print them all in last column .
How can i do this using VBA code

 

[DanteAmor]

Thank you so much sir. It worked exactly how i needed

I'm glad to help you. Thanks for the feedback.