(Solved) – Can’t set filter in pivot table – error 424


I’m working on a file with a pivot table. I need to select some filter with VBA, so I used the “macro recorder” to make this part. The whole macro, launch from a specific workbook (let’s call it the main file), does the following things :

  1. ask for a folder
  2. for each file in the folder, open the file
  3. select a given worksheet
  4. set filters (see below)
  5. save some of the data into the main file

Once I’m in the workbook that just has been opened, that’s what I want to do

Set wb = Workbooks.Open(strFolder & strFileName)

With ActiveWorkbook.SlicerCaches("Slicer_1")
    .SlicerItems("item1").Selected = True
    .SlicerItems("item2").Selected = False
    .SlicerItems("item3").Selected = False
End With

With AcvtiveSheet.PivotTables("my_table_name")
    .PivotFields("name1").CurrentPage = "value1"
    .PivotFields("name2").CurrentPage = "value2"
    .PivotFields("name3").CurrentPage = "value3"
End With

The first part with the SlicerCaches works well. But I can’t go through the second part. I got an error 424 “object required” However, it works, not always, when I launch this part from a module in the workbook that contains the table (not the main file).

Have you got any idea to solve this?

Thanks a lot for your help

Leave a Reply

Your email address will not be published. Required fields are marked *