Stuck with Pivot Table Filter based on cell value - VBA macro

[smsko]

Hello,

I've been trying to find a solution to the problem I'd like to resolve, but
I'm stuck with 7-8 different examples from different posts and I've been
unable to modify one of them to work with my pivot table with sales data.

Sheet name: Report
Pivot table name: PivotTable1
Report filter name: Weeknum

I'd like to create a macro that will clear Weeknum filter
and set it to the value from the cell F1.

I have set up differents pivot tables and by doing the change of filter
based on filter value, I'd not have to modify the pivot table each
week ( if I set cell value to show current weeknum ) or in other cases
each day.

Thanks.

 

[Andrew Poulsom]

What have you tried? The macro recorder will get you started.

 

[smsko]

Thanks for the suggestion.

I'm sure we'll find what I need quickly.

I have started macro recorder as you said an now it's much clearer on what the next step should be.

Macro:

Sub Macro3()
'
' Macro3 Macro
'


'
ActiveSheet.PivotTables("PivotTable1").PivotFields("Weeknum").CurrentPage = "7"
End Sub

--------------

I have entered manually "7". It is now clear that when macro is run, the value changes to Weeknum 7.

How should I modify the macro to change from entered value "7" to value in cell F1?

I really appreciate your answer and help.

Thanks.

 

[Andrew Poulsom]

Maybe try:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Weeknum").CurrentPage = Cstr(Range("F1").Value)

 

[smsko]

Maybe try:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Weeknum").CurrentPage = Cstr(Range("F1").Value)

It works!

Great. Thanks a lot.

 

[electrifiednuisance]

I have a similar problem where I have to manipulate the filter of PivotTable(2) according to the filter in PivotTable (1) (both have same filters). I have tried many things including the one above but stuck with Type Mismatch Error.

Thank you

 

[Andrew Poulsom]

Welcome to MrExcel.

What have you tried that causes an error exactly?

 

[Muhammad Ishack]

Welcome to MrExcel.

What have you tried that causes an error exactly?

Hello andrew,

really need your help to create a macro with macro recorder.I am also the stuck up with pivot table in my case the value which i need to filter is in another sheet..I saw your code but ita for the value in the same sheet.so could you please help me with the code for value in another sheet. for eg the value is in F3 cell of sheet 4