CODEWITHSUNDEEP
excelvbadateloopspivot-table
Ramify
Ramify

Reputation: 39

Excel Macro ordering PivotItems

Background: I have a pivot table in Excel with "Date" as an XLColumn field. The source data is always changing and I noticed when new data would be added the pivot table would NOT order the pivot items for filtering by date. It would add it to the end as a string. I have since changed the source data and made sure the Date column are indeed dates.

1st Thing: I need to order the date pivot items programmatically in VBA. That is a user can filter the dates which are listed in order even when new dates are added (older to more recent).

2nd Thing: I want to loop through the date pivot items, showing the the 1st and 2nd, then 2nd and 3rd, then 3rd and 4th etc.

Something like:

-code that hides all pivot items
Pf.pivotitems (i).visible = true (12/04/2012)
Pf.pivotitems(i + 1).visible = true (19/04/2012)
i = i + 1

This has been returning an error on me, can't set visible property of pivot item.

Thanks,

Any further information required please ask.

Ramify

Upvotes: 0

Views: 2111

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Like this?

1st Thing: I need to order the date pivot items programmatically in VBA. That is a user can filter the dates which are listed in order even when new dates are added (older to more recent).

'~~> Change Sheet1 with the relevant sheet name
With Sheets("Sheet1")
    '~~> Change PivotTable1 and DateField to the respective pivot and field name.
    .PivotTables("PivotTable1").PivotFields("DateField").AutoSort xlAscending, "DateField"
End With

2nd Thing: I want to loop through the date pivot items, showing the the 1st and 2nd, then 2nd and 3rd, then 3rd and 4th etc. 

Pf.PivotItems(i).Visible= True
Pf.PivotItems(i + 1).Visible= True

Upvotes: 1

Related Questions