VBA to apply auto update pivot filter when cell value changes in another sheet

[azazafzal]

Hi,

I've a workbook with two sheets. Sheet1 has PivotTable1 and Sheet2 has the cell (D2) with vlookup formula in it. Is it possible to get the pivot table filter automatically updated everytime when the value in Sheet2 cell D2 is changed? Sheet2 would be hidden, so no manual click would be done on Cell D2

 

[vletm]

azazafzal
How would You click to hidden Sheet2's cell D2?
Even a sample file would open Your thread better.
So far, You could test to save next code to Sheet1's code page:

Private Sub Worksheet_Activate()
    ActiveWorkbook.RefreshAll
End Sub

It would refresh Your Pivot then You'll activate Your Sheet1 again.
Sheet2 would be hidden, so no manual click would be done on Cell D2

 

[azazafzal]

Hi vletm
I would not be clicking on Sheet2's cell D2? It is linked to list Box (Form control). So whatever value I click on, the cell value will automatically get changed to it. I have attached a sample file here
'Dashboard sheet is the where I have the pivot table and list box. My pivot has twp filter, 1st one is 'Employee Name' and 2nd is 'Bonus'. By default filter would be set on 'Bonus' field to '0' and only 'Employee Name' filter would change based on selection made on List Box. For example if I click on 'Peter' in List Box then filter in Pivot Employee Name field 'Peter' should be selected keeping 'Bonus' field freezed to '0'.

Alternate Option
I could use a table in place of Pivot (in "Dashboard" sheet) if this could be easy to apply filter. Then my criteria would be only 'Employee Name' based on selection made in 'List Box'

 

[vletm]

azazafzal
I copied parts of Your original writings as You've written those ...
and next You wrote that You do something different - I see.
It's a challenge to try to figure --- what? Okay?
That's why I gotta skip Your the newest writings.

>> click that Your listbox in Dashboard-sheet and see ... it will this work someway as You had an idea. <<

 

[azazafzal]

Hi vletm,

The file you shared does the work, however when i tried the same vba on my file it doesn't do anything. Could you please let me know how could I use this on my file?

 

[vletm]

azazafzal
Your 'my file' should have same named sheets as my sent sheets
as well as Your used features ( = pivot-table position and listbox's cell link ).
So far, Your original 'file' #1 and sent #3 file were different.
Your give sample files should be same with Your 'my file', otherwise You should able to explain differences.
The key is 'connection' with Your give image of used cells and control of that my made code.
If You noticed ... I gave instructions between >> << with my previous reply (#4).
Of course, I could start to guess - what kind of file do You have ... but there would be too many possibilities ... too many!
... plan B: use that my sent file with Your normal data.

 

[azazafzal]

Unfortunately I could not replace my file as it has huge data which is calculated from different sources. I tried using table instead of pivot and am successful to some extent. The only issue now is when I click on any value in 'List Box (Form Control), the table would only apply the changes if I click on any another sheet and then return to 'Dashboard' sheet. Can you suggest something that would make the filter change the moment I click on any value in the 'List Box'?

 

[vletm]

azazafzal
Did You try to read my previous reply?
Did You try to figure what did I try to write?

Your the newest file left and my sent file right

... do those look same?
... do Pivot-table have same features?
If Your files are like an ameba ... then there would need a lot of code ... a lot!

Why Your sample file are different by layout ... than Your 'huge' file?
That's Your challenge!

 

[azazafzal]

Hi Vletm,

First of all let me thank you for taking out time in helping me with this issue. Really appreciate your patience and support..!
I have checked your file and have used same VBA in my sample file (which I attached at #3) and have also converted my sample file to .xlsb but it does not work for me. All fields, Header, Cell value is same as the one which you shared but still no luck. Could you tell me which step am I missing?

I have attached the file again

 

[vletm]

azazafzal
? is a question mark.
if that is the last mark in sentence then it's a question.
I try to make questions which would help ... You.
Without answers, it's also challenge to try to help ... You.

Could you tell me which step am I missing?
... I've TRIED to write few times...
The key is 'connection' with Your give image of used cells and control of that my made code.
>> click that Your listbox in Dashboard-sheet and see ... it will this work someway as You had an idea. <<
So sorry, I gotta make more questions...
If You would get an answer from 1+2= ? that's 3 ... then You won't learn!

1) After which action something should happen?
... after You click Your listbox -- okay?
2) So, then there have to some kind of connection which 'click' and 'my code' -- okay?
3) Have You found that my code?
... You should have, because You haven't wondered that at all!
4) Move Your mouse over Your listbox
5) Make RIGHT click (if You're using left hand mouse, then do opposite action)
6) There is option: 'Assign Macro...'
--- Can You see/find it?

>> this is the step that You're missing!
>> You click Your listbox and after that 'my code' will run.
(( It's almost same as in football game. You gotta have a ball that You could kick it to the goal. ))

 

[azazafzal]

How silly of me...!!

It's working now Thank you very much vletm

 

[vletm]

azazafzal
Without answers, it's also challenge to try to help ... You.