MACRO - Pivot filter based on cell reference

Hi all,

I'm currently in a new 'phase' of my excel career: connecting a cell(variable) with a pivot table filter using a macro.
I've been looking around for quite a while and testing a few macro's but nothing worked out yet.. I would be great if you guy's could help me out!

Could someone help me to write the right macro to filter a pivot based on 3 criteria's? (attachment for more detail)

Macro to change one pivot filter based on datainput from another cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = Worksheets("Pivots").PivotTables("PivotTable9")
Set Field = pt.PivotFields("Ship To Postal Code")
NewCat = Worksheets("Pivots").Range("D4").Value

Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub


Thanks you so much in advance!

Cheers

DavidPivot filters.xlsx

Hi Maikoz,

This is the start of Pivot table - your might need to tweek stuff here and there...

Formula:

Please Login or Register  to view this content.

hope this helps

Cheers
Lex

Hi Lex thanks man for helping me!

Private Sub Worksheet_SelectionChange(ByVal Target As Range) <- Error here!

If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub

Dim PT01 As PivotTable
Dim PTCache As PivotCache
Dim FinalRow As Long
Dim FinalCol As Long
Dim PRange As Range

Sheets("Pivots").Select 'Your Data Tab and make sure that your header is in row 1

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = Cells(1, 1).Resize(FinalRow, FinalCol)

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PRange)
Sheets("Pivots").Select 'Your Summary tab
Set PT01 = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, tabledestination:=Range("A3"))
With PT01
.PivotFields("Billing Date.Year").Orientation = xlPageField
.PivotFields("Ship To Address").Orientation = xlPageField
.PivotFields("Ship To Postal Code").Orientation = xlPageField
.PivotFields("Your Row Name").Orientation = xlRowField
End With

With PT01.PivotFields("What you calculate")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00_ ;[Red]-0.00 "
End With

What i forgot to tell you that i don't have a data sheet... Pivot table is based on an OLAP query.. Is there a workaround?

Thanks again!
Maik