Pivottable filter change on cell formula output

Thoie14

New Member
Joined
Feb 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have been trying to make my worksheets more dynamic for which I believe applying VBA is necessary in my specification situation. Unfortunately I consider myself somewhat a beginner in VBA, i.e. I understand the code by reading it but I am not able to reproduce the code itself. Hope you guys can help.

I want the pivottable filters to refresh based on a cell value and select that cell value. Code I found online has only been able to reproduce it when the code is manually entered ( Filtering Pivot Table Based on a Cell Value ) . When I include a formula in this same cell reproducing a value from another worksheet, no refreshing of the filters/pivottable occurs, which is probably due to the code. Looking for a fix in the code or workaround.

Situation is as follows,
Worksheet: "Var code".
Cell Range to select: "o5" (This cell includes the formula lookup)
Field/Category to change: "Period"
Pivottable name: "PivotTableVAR"

Thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
The code I currently copied from the interwebs and modified slightly is below. There seems to be one piece missing to combine the rows 2-6 to the latter.

Refer to the code below.
Private Sub Worksheet_Change(ByVal Target As Range)
Static oldval
If Worksheets("Variable dataset transformed").Range("O5").Value <> oldval Then
oldval = Worksheets("Variable dataset transformed").Range("O5").Value
'
'rest of your code here
'

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim WS As Worksheet
Dim pt As PivotTable
Const strField As String = "Periode" 'This is the name of the pivot table filed you wish to change

If Target.Address = "$O$5" Then

For Each WS In ThisWorkbook.Worksheets
For Each pt In WS.PivotTables
With pt.PageFields(strField)
.ClearAllFilters
.CurrentPage = Target.Value
End With
Next pt
Next WS
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top