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

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Use this.
On the red line enter the names of the fields that should not be presented as a sum.

Code:
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

Active Member
Joined
Mar 26, 2019
Messages
308
Use this.
On the red line enter the names of the fields that should not be presented as a sum.

Code:
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

Active Member
Joined
Mar 26, 2019
Messages
308
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,052
Messages
5,599,511
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top