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

smsko

New Member
Joined
Jan 2, 2014
Messages
11
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

smsko

New Member
Joined
Jan 2, 2014
Messages
11
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe try:

Rich (BB code):
ActiveSheet.PivotTables("PivotTable1").PivotFields("Weeknum").CurrentPage = Cstr(Range("F1").Value)
 

smsko

New Member
Joined
Jan 2, 2014
Messages
11

ADVERTISEMENT

Maybe try:

Rich (BB code):
ActiveSheet.PivotTables("PivotTable1").PivotFields("Weeknum").CurrentPage = Cstr(Range("F1").Value)

It works!

Great. Thanks a lot.
 

electrifiednuisance

New Member
Joined
Jan 13, 2015
Messages
1
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
 

Muhammad Ishack

New Member
Joined
Apr 11, 2017
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,472
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top