using cell data to filter pivot table (date field)

[riverstar]

I am using a macro to update the a filter on a pivot table. The below script works great when the field is text however I can not get it to work with a date value.

I have tried several differnt changes but cannot get it to work. It will clear the filter but does not appear to recognise the date value from the cell as a valid selection. I have even tried to converting the date to a text value first with no luck.

My Pivot table (Campaigns) has two filters (Group and Date) which both need to be updated from different cells (A1 and B1). Below is the script that works and updates the Group filter.

Any help would be much appreciated. I am new to playing with VBA and seem to be completely lost with this one.


Sub Apply_Reporting_Group_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterReportingGroup As String

Set pvtTable = Worksheets("Sheet1").PivotTables("Campaigns")
Set pvtField = pvtTable.PivotFields("Reporting Group")

filterReportingGroup = Worksheets("Data Control").Range("A1")

For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterReportingGroup Then
pvtField.CurrentPage = filterReportingGroup
Exit For
End If
Next pvtItem
End Sub

 

[tjessome]

Hi Alpha,

That seems to be doing the trick on the DNB_Summary. However, I believe that since my DB_Summary pivot is located to the left of the DNB_Summary, when the macro attempts to ClearAllFilters on DB_Summary it returns an error because two pivot tables cannot overlap each other. I have tried running the macro without ClearAllFilters, but it appears that this needs to be initiated before this macro will work.

Any additional thoughts?

 

[AlphaFrog]

Hi Alpha,

That seems to be doing the trick on the DNB_Summary. However, I believe that since my DB_Summary pivot is located to the left of the DNB_Summary, when the macro attempts to ClearAllFilters on DB_Summary it returns an error because two pivot tables cannot overlap each other. I have tried running the macro without ClearAllFilters, but it appears that this needs to be initiated before this macro will work.

Any additional thoughts?

None other than orienting the two pivot tables vertically instead of horizontally.

You're welcome.

 

[tjessome]

Unfortunately, I think that will cause the same issue as the pivot table will expand vertically as well.

You're welcome.

Not quite sure if that was sarcastic or not, but I do appreciate your time!

Thanks anyways!

 

[tjessome]

Unfortunately, I think that will cause the same issue as the pivot table will expand vertically as well.

You're welcome.

Not quite sure if that was sarcastic or not, but I do appreciate your time!

Thanks anyways!

 

[tjessome]

I think I have developed a good way to perform my task.

I have inputted a Slicer into my worksheet that can be used to select the appropriate date that you would like to view. I have then linked this slicer to both pivot tables within this worksheet, which allows you to change the date filter on both pivot tables simultaneously.

Additionally, when the worksheet with the pivot tables is activated, I have developed the following code to filter the slicer to the current date:

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = False
If Range("D7").Value = Range("G7").Value Then
'D7 contains the date that is currently filtered; G7 is today's date; if equal, exit sub
Exit Sub
Else
    With ActiveWorkbook.SlicerCaches("Slicer_Date")
        .SlicerItems(Format(Range("G7").Value, "m/d/yyyy")).Selected = True 'Filter slicer to today's date
        .SlicerItems(Format(Range("D7").Value, "m/d/yyyy")).Selected = False 'Deselect the preivously selected date
    End With
End If
Application.ScreenUpdating = True
End Sub

In order for this code to work consistently, your pivot tables need to be separated by at least one column so that they do not overlap each other when more than one date is selected.

Hope this helps some excel nerd out there some day!

 

[Jkimster145]

Hi Guys,

I have been stuck on this thread for the past couple hours and have been trying desperately to get this to work. I need a filter, like river star had mentioned, that will hold A1:A20 on a separate tab where I can type in Customer Numbers and have it filter my Pivot Table. I am extremely new to VBA, so any help would be appreciated.

Sheet 1 holds my pivot table
Sheet 2 is my list that I want to filter by
Pivot Table is PivotTable
and pvtTable.PivotFields("[Customer].[Branch Customer Number].[Branch Customer Number]") is my pivot field.

 

[Jkimster145]

I took Riverstar's code and tried to make it work for my report with no luck. Below is what I have so far.

Sub Apply_Reporting_Group_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterReportingGroup As String


Set pvtTable = Worksheets("Sheet1").PivotTables("PivotTable")
Set pvtField = pvtTable.PivotFields("[Customer].[Branch Customer Number].[Branch Customer Number]")


filterReportingGroup = Worksheets("1").Range("B1").Value


For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterReportingGroup Then
pvtField.CurrentPage = filterReportingGroup
Exit For
End If
Next pvtItem
End Sub

 

[MacroMan007]

Thank you for this post... I tried all other kinds and it kept erroring.. until I came across this one and it works perfectly.

With Sheet5.PivotTables("PCRD_Pivot").PivotFields("PC Date of Review")
.ClearAllFilters
.PivotFilters.Add Type:=xlSpecificDate, Value1:=Format(Range("I1").Value, "m/d/yyyy")
End With

Thanks again..
Jay

 

[kcmuppet]

Is there a way to make this approach work for all pivot tables on the sheet, without having to name them?

With Sheet5.PivotTables(____Something in here that refers to all pivot tables___).PivotFields("Date")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlSpecificDate, Value1:=Format(Range("D7").Value, "m/d/yyyy")
End With

 

[AlphaFrog]

Is there a way to make this approach work for all pivot tables on the sheet, without having to name them?

    Dim pt As PivotTable
    For Each pt In Sheet5.PivotTables
        With pt.PivotFields("Date")
            .ClearAllFilters
            .PivotFilters.Add Type:=xlSpecificDate, Value1:=Format(Range("D7").Value, "m/d/yyyy")
        End With
    Next pt