Filtering a Pivot Table based on multiple cells values

[rwrig87]

So I'm attempting to create a master data sheet that has a data cube in the form of a pivot table. Long term I will like to use other files to come back to this file and filter the cube and copy the data and paste it as values but in the short term I'm working on the aspect of the VBA code that filters the pivot table based on three cell selections. So far I'm able to filter for the location which is in cell K3, Now I'm trying to filter for the date range on the same pivot table. I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("K2:K3")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As Variant
Dim Field1 As Variant
Dim NewCat As String
Dim DateCat As String
Dim DateCat1 As String

Set pt = Worksheets("Fact Trans").PivotTables("PivotTable1")
Set Field = pt.PivotFields("[Locations].[Loc Name].[Location Name]")
Set Field1 = pt.PivotFields("[Date of Service].[Mth Year].[Mth Year]")
NewCat = Worksheets("Fact Trans").Range("K3").Value
DateCat = Worksheets("Fact Trans").Range("K4").Value
DateCat1 = Worksheets("Fact Trans").Range("K5").Value

With ActiveSheet.PivotTables("PivotTable1").PivotFields("[Locations].[Loc Name].[Location Name]")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionEquals, Value1:=ActiveSheet.Range("K3").Value
    .PivotFilters.Add Type:=xlDateBetween, Value1:=ActiveSheet.Range("K4").Text, Value2:=ActiveSheet.Range("K5").Text
End With

End Sub

Any insight on how I can get this vba to work for my date range?

 

[rwrig87]

Also please excuse the noise in the file I was working on multiple solutions such as slicers and other codes.

 

[Nebu]

Hi:

In your pivot table you have not saved the background data so it will not refresh or filter at my end as it is not connected to the external data source from which it is drawing data from. But I would suggest to use slicers, if excel is providing with the native function of slicers why you want to write a macro for doing multiple filters beats me.

Thanks

 

[rwrig87]

@Nebu Normally I will use slicers but I want this sheet to be a master data sheet that I will use other sheets to query information from the pivot table. For example, I will have another workbook that will prompt you to enter selections, than the macro will open this file that I'm referencing paste those selections in the three cells I'm trying to filter for, filter the pivot table copy the data and paste as values in the other workbook. Getting this code down is just the first step.

 

[Nebu]

Hi:

Your pivot table is not working at my end, as I had mentioned in myearlier post.
I am not sure why you have this portion of the code

Set pt = Worksheets("Fact Trans").PivotTables("PivotTable1")
Set Field = pt.PivotFields("[Locations].[Loc Name].[Location Name]")
Set Field1 = pt.PivotFields("[Date of Service].[Mth Year].[Mth Year]")
NewCat = Worksheets("Fact Trans").Range("K3").Value
DateCat = Worksheets("Fact Trans").Range("K4").Value
DateCat1 = Worksheets("Fact Trans").Range("K5").Value

You are not using any of these variables to filter the pivot.

I would suggest you to create a sample file with a sample pivot of the same layout without connection from an external source and upload it here. I will be able to code it for you.

Thanks

 

[Bhavesh_1970]

Hi Nebu,
I have a XL with 2 pivots... i m trying to use a value from 3rd sheet to have filter on both pivots.... but not working.... i am not good at VBA... can you help? can i share my file to you ?

 

[vletm]

Bhavesh_1970
Open a new case.
https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/