VBA Pivots

[zkgkilla1]

Hi All,

I have been working on a project for nearly 2-4 months. I have completed the project and it is working perfectly (VBA Report). I have over 8000 lines of code so you could say I know a bit about VBA. I have done alot of other programs too.

Now to the matter in hand. I know how to select pivot items using vba and have already got this.

However can you select a pivot item which is not there but will be in the future. I have created this report to last a few good years but cannot atm because of this. (Now I have noticed that if it does not find the pivot item, it creates it. Leaving the actual pivot item to resolve to number two. For example say I select Q1 2014 using vba. When it does not find it, it creates Q1 2014. As soon as that data is available it puts it as Q1 20142 as a entity with Q1 2014 already exists but with no data) This is my main issue.

My report uses pivots alot I must be able to select dynamic pivot items and if not what is the solution?

Please find the code below.

'I use this to select data
Sheets("Q1 Open").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = _
"(Blank)"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = _
"Q1 2014"

'-----------------------------------------------------------------------------------
'this is another technique to select multiple items
Sheets("Win Rate").Select

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarter Win/Loss date")
On Error Resume Next
.PivotItems("Q1 2014").Visible = True
.PivotItems("Q2 2014").Visible = True
.PivotItems("Q3 2014").Visible = True
.PivotItems("Q4 2014").Visible = True
.PivotItems("(blank)").Visible = True
End With

 

[zkgkilla1]

After hours of searching only this came close to what I want but it is still not what I want.

PLEASE HELP!


Sub FilterDateField()
Dim pi As PivotItem ,ws As Worksheet , SixWeeksAgo As Date,PT As PivotField

Application.Screenupdating = False
Application.EnableEvents = False
SixWeeksAgo = Now() - 42

Set ws = ActiveSheet
Set PT = ws.PivotTables("PivotTable2").PivotFields("date")

For Each pi In PT.PivotItems

If pi < SixWeeksAgo Then pi.Visible = False
Next pi
Set ws = Nothing: Set PT = Nothing
Application.Screenupdating = True
Application.EnableEvents = True
End Sub

 

[zkgkilla1]

SOLVED!!

Need to clear the filters everytime... by deselecting all but one then selecting what u need... and for the above just did a IF statement to do the job so if Q1 do Q1, if Q2 do Q1 and Q2... and so on.