Pivot table filtered from cell reference

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
84
Hi All

I have an issue where I need to have 5 pivot tables to automatically filterbased on a single cell reference.

The cell (K1) is a date that is subject to changedepending on the date the report is required for.
The 5 pivot charts all run from the same data table connectionwith slightly difference breakdowns.
The data table column heading to be filtered is “Eff Date”, but shows up on the pivot table as "Row Label". AlthoughI have had a look I have yet to find a solution that seems to work.

The VBA I have for one pivot currently is:

Sub Pivot_Filtering()
Dim Report As String
Report = Range("K1").Value

Dim PvtTbl1 As PivotTable
Set PvtTbl1 = Worksheets("Pivot").PivotTables("PivotTable1")

PvtTbl.PivotFields("Eff Date").PivotFilters.Add Type:=xlCaptionContains, Value1:=Report

End Sub

 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,965
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top