(Macro) clear filter and refresh Pivot Table data without using ActiveSheet.PivotTables

- test repost -

Hi,

I've set the following names to 3 cells in Sheet1


CalenderPreviousName ... A1
CalenderCurrentName ... B1
CalenderNextName ... C1


I've filled:

2016 in A1
2017 in B1
2018 in C1

Obviously I've did that to save time creating the same file next year and only file the following years later on like 2017, 2018, 2019 in A1, B1, C1

Then I've created a table in the same Sheet1 "$A$3:$P$19"

There is a filed in this table for DATE and its cell formatted as date.

Then I've created a Pivot Table in Sheet2using the Table in Sheet1

Now when I open the PivotTable and open the filter menu for Date filed I've found 5 items ( < 2016 , 2016 , 2017 , 2018 , > 2018 ) .

I want to make a macro to choose only the items represent CalenderPreviousName, CalenderCurrentName, CalenderNextName.

When I recorded a macro and tried to edit it I've found the recorded code includes the value of the cells "2016, 2017, 2018" and I did tried to replace it with CalenderPreviousName, CalenderCurrentName, CalenderNextName but it resulted with an error.

So how to set this type of macro?

Hey MGadAllah,

If all you want to do is pass the value in the cells to the macro, use in your code Sheets("Name of your sheet).Range("Cell Address").Value.
I believe in your case would be Sheets("Sheet1").Range("A1").Value, Sheets("Sheet1").Range("B1").Value and Sheets("Sheet1").Range("C1").Value.

Hope any one may help

-later edit-
This is the macro I've recorded

Please Login or Register  to view this content.

So I do not want to use the <6/30/2016 or >6/2/2018 but I want to use the NAME I've set for it, which are :
CalenderPreviousName or CalenderCurrentName or CalenderNextName

The reason is that later on if I want to work for later consequence years, I want everything to be correctly sorted without editing anything, and just fill in the correct years in the 3 cells
CalenderPreviousName ... A1
CalenderCurrentName ... B1
CalenderNextName ... C1

So how to sort it correctly into the macro?

Thanks

Hello again MGadAllah,

In this case where you want to pass the value of a named range in your code, all you gotta do is use Range("Name of your range").Value.
So, as your example, you would have:

A1 = Range("CalenderPreviousName")
B1 = Range("CalenderCurrentName")
C1 = Range("CalenderNextName")

I've opened your worksheet in my machine and sadly, due to language dificulties, I couldn't understand your data.
Even though, I think I could guess what you wanted to do, so I've made a little sample sheet and wrote a bit of code that I think will help you.
In this case, I think the best approach would be to use the same name for all year field in all pivot tables and update the setting of the filter on that field on all tables.
In the Data sheet I've named cells N1 to N3 with the names you used on your named ranges.

This is the code I've used, and I'm attaching the sample data below. Hope it helps you.

Please Login or Register  to view this content.

Best regards!

Thanks for replying me
I've recreated the whole work book with English items instead of Arabic.
Now you can open and read and understand
In the report sheet, when you open the Years filter you can see 3 years which are 2016-20017-2018
In the daily Entries sheet a the top you can find 3 cells named:
CalenderPreviousYear
CalenderCurrentYear
CalenderNextYear
So the macro I want to make to filter the years I want it to include the names instead of values.
I'm creating a new sheet each new year, so I want to just fill in the years in these 3 cells without changing anything in the pivot table or the macro I will create.
This is why I want to make the macro include Name instead of its Value
This is the macro I've recorded

Please Login or Register  to view this content.

So it contains dates which will not work later on if I changed years to 2017-20018-2019
This is why I've asked about a way to include the name instead of value.
I am searching since yesterday and can not find a way to replace PivotItem with something else
Also tried to play with XlPivotFilterType as I've posted about it here bu no luck at all.
I can use he NAME I've set for the cells in any equation as fine.
I hope that I was able to clear my point.
Thanks

Hope any one may help