[VBA Expert Need] Filter Pivot Table based on list data from another sheet

[ramozpratama]

Dear Mr Excel,

Need help with my VBA Code in macro excel.

Here is my pivot table field list (table pivot name --> "CASA2017" and field name "CIF17")

I want to auto filter this pivot table with cell value in A1:A30 but in another sheets (sheet1).
I Try this code, but only read filter based on A1 value only

Sub Filter17()
Dim PT17 As PivotTable
Dim PF17 As PivotField

Set PT17 = Worksheets("Sheet2").PivotTables("CASA2017")
Set PF17 = PT17.PivotFields("CIF17")

PT17.ClearAllFilters
PT17.AllowMultipleFilters = True
PF17.EnableMultiplePageItems = True
PF17.CurrentPage = Worksheets("Sheet1").Range("A1:A30").Value
End Sub

Please help to correct my code.. Thank you so much.. Sorry for bad english

 

[DanteAmor]

Hi @ramozpratama, welcome to the board!

Try this

Sub Filter17()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim PT17 As PivotTable, PF17 As PivotField, pItem As PivotItem
  Dim rng As Range, c As Range, f As Range, n As Long
 
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  Set PT17 = sh2.PivotTables("CASA2017")
  Set PF17 = PT17.PivotFields("CIF17")
  Set rng = sh1.Range("A2:A" & sh1.Range("A" & Rows.Count).End(xlUp).Row)
 
  PT17.ClearAllFilters
  For Each pItem In PF17.PivotItems
    Set f = rng.Find(pItem.Value, , xlValues, xlWhole)
    If f Is Nothing Then
      n = n + 1
      If n < PF17.PivotItems.Count Then
        PF17.PivotItems(pItem.Value).Visible = False
      Else
        MsgBox "You are trying to hide all items"
        PF17.ClearAllFilters
      End If
    End If
  Next
End Sub

 

[ramozpratama]

Thank you Mr Dante,

I'll try your code, but i have error message

Sub Test17()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim PT17 As PivotTable, PF17 As PivotField, pItem As PivotItem
Dim rng As Range, c As Range, f As Range, n As Long

Application.ScreenUpdating = False
Set sh1 = Sheets("MainMenu")
Set sh2 = Sheets("2017")
Set PT17 = sh2.PivotTables("CASA2017")
Set PF17 = PT17.PivotFields("CIF17")
Set rng = sh1.Range("C4:C" & sh1.Range("C" & Rows.Count).End(xlUp).Row)

PT17.ClearAllFilters
For Each pItem In PF17.PivotItems --> error in here
Set f = rng.Find(pItem.Value, , xlValues, xlWhole)
If f Is Nothing Then
n = n + 1
If n < PF17.PivotItems.Count Then
PF17.PivotItems(pItem.Value).Visible = False
Else
MsgBox "You are trying to hide all items"
PF17.ClearAllFilters
End If
End If
Next
End Sub

is there any mistakes that i do sir?
this is pivot table that i create in sheet ("2017")

and this menu in sheet ("MainMenu") for parameter filter to the pivot table

Thank you so much, and really appreciate for your help sir..

 

[DanteAmor]

Check my test file

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[ramozpratama]

I'll Try your file and its works sir..
But, in your file the pivot table is came from table in workbook. what if i create the pivot table from a connection database to microsoft acces sir?
It's means i dont have pivot item right? thats the proble sir?

 

[ramozpratama]

I'll Try your file and its works sir..
But, in your file the pivot table is came from table in workbook. what if i create the pivot table from a connection database to microsoft acces sir?
It's means i dont have pivot item right? thats the proble sir?

I mean, my pivot table created based on connection only from another file. is it possible to code like your files have if table doesn't exist from the same workbook?

 

[DanteAmor]

The table works with the data in the table.
I have no way of testing with an access connection, therefore I could not come up with a solution with that type of table.