justinua
03-27-2015, 07:21 AM
Hello! I'm relatively new to VBA and I'm trying to set a filter to a pivot table so that it filters out any dates before a certain date. That certain date varies based on whatever a user typed into another cell. They way that is set-up is that in one cell, the user will enter the name of the month. Depending on whatever they enter in that cell, the adjacent cell has a formula that translates the month name into the short date. For example, if the user types "March" in cell D3, cell E3 is will say "3/1/15". If D3 says "June" then E3 will say "6/1/15."
What I am looking to do is create a code that will filter my pivot table to only show data associated with dates after whatever is in E3 (ex. 6/1/15). All dates before whatever is in E3 should not be visible. I have been able to get to the point where it is not showing any dates before the E3 date; however for some reason it is also filtering out dates that are occur after that E3 date. For example, if the E3 date is set to 3/1/15, it filters out any prior dates, but it also filters out 10/1/15-2/1/16, 10/1/16-2/1/17, 10/1/17-2/1/18. Below is the code that I have been using:
Pivot_sht.PivotTables(PivotName).RefreshTable
Dim dt4 As String
dt4 = Sheets("Start").Range("E3").Value
Dim pf4 As PivotField
Dim pf5 As PivotField
Dim pf6 As PivotField
Dim pi4 As PivotItem
Set pf4 = Sheets("DPA CON comparison pivot").PivotTables("PivotTable9").PivotFields("FORECAST_DATE")
Set pf5 = Sheets("DPA CON comparison pivot").PivotTables("PivotTable9").PivotFields("TAG")
Set pf6 = Sheets("DPA CON comparison pivot").PivotTables("PivotTable9").PivotFields("DPA")
'Sets filter so that it will not show any dates before that occurred prior to the date listed on the "Start" Tab in cell E3
For Each pi4 In pf4.PivotItems
If pi4 <= dt4 Then
pi4.Visible = False
Else
pi4.Visible = True
End If
Next pi4
From what I've noticed, the code seems to be working up until the last 4 lines of code (starting with "Else"). At that point, the prior dates had been filtered out; however it is not making sure that all dates after that are visible. In addition, the last line of code ("Next pi4") is where I notice that it filters out the dates between October and February. Thank you and I appreciate any help you can provide!
What I am looking to do is create a code that will filter my pivot table to only show data associated with dates after whatever is in E3 (ex. 6/1/15). All dates before whatever is in E3 should not be visible. I have been able to get to the point where it is not showing any dates before the E3 date; however for some reason it is also filtering out dates that are occur after that E3 date. For example, if the E3 date is set to 3/1/15, it filters out any prior dates, but it also filters out 10/1/15-2/1/16, 10/1/16-2/1/17, 10/1/17-2/1/18. Below is the code that I have been using:
Pivot_sht.PivotTables(PivotName).RefreshTable
Dim dt4 As String
dt4 = Sheets("Start").Range("E3").Value
Dim pf4 As PivotField
Dim pf5 As PivotField
Dim pf6 As PivotField
Dim pi4 As PivotItem
Set pf4 = Sheets("DPA CON comparison pivot").PivotTables("PivotTable9").PivotFields("FORECAST_DATE")
Set pf5 = Sheets("DPA CON comparison pivot").PivotTables("PivotTable9").PivotFields("TAG")
Set pf6 = Sheets("DPA CON comparison pivot").PivotTables("PivotTable9").PivotFields("DPA")
'Sets filter so that it will not show any dates before that occurred prior to the date listed on the "Start" Tab in cell E3
For Each pi4 In pf4.PivotItems
If pi4 <= dt4 Then
pi4.Visible = False
Else
pi4.Visible = True
End If
Next pi4
From what I've noticed, the code seems to be working up until the last 4 lines of code (starting with "Else"). At that point, the prior dates had been filtered out; however it is not making sure that all dates after that are visible. In addition, the last line of code ("Next pi4") is where I notice that it filters out the dates between October and February. Thank you and I appreciate any help you can provide!