Pivot table VBA - weird behaviour looping through field items

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
I'm having trouble setting fields in VBA.

As part of seeing what is going on, I put in some code to loop through the pivot items in the pivot field and debug.print the values. It has done it, but the debug.print has generated the same list twice and one has blank as an option. Can anyone explain that, is it something do do with the pivotcache?

VBA Code:
                For Each pf In pt.PageFields
                    If pf.Name = "Month" Then
                        For Each pi In pf.PivotItems
                            Debug.Print ws.Name & "-" & pt.Name & "-" & pf.Name & "-" & pi.Name
                            Debug.Print pi.Visible
                        Next pi

and this is what got printed (Months 3-12 removed)

Rich (BB code):
Sales Summary (1)-PivotTable1-Month-1
True
Sales Summary (1)-PivotTable1-Month-2
True
Sales Summary (1)-PivotTable1-Month-1
True
Sales Summary (1)-PivotTable1-Month-2
True
Sales Summary (1)-PivotTable1-Month-(blank)
True
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,214,590
Messages
6,120,423
Members
448,961
Latest member
nzskater

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
Back
Top