VBA to change Value Fields of Pivot Tables with Slicer

[anariskenderli]

Hello, everyone.

I have a problem on VBA that I can not solve for days. I would be very grateful if anyone could help me on this issue.
ByYear and ByStore Sheets have pivot tables which both names are PivotTable1. I need both pivot tables' Value fields to be changed by slicer without connection with each other. So, in Metadata sheet I created two pivot tables based on two tables and inserted two slicers and replaced them to ByYear and ByStore Sheets. At last I modified a VBA code I found on Internet, put it in ThisWorkbook but it didn't work.
Thanks in advance for any help.

The link for sample xlsm file is:

TEST.xlsm

drive.google.com

The modified VBA code is:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim ptMain As PivotTable
Dim pfMeasure As PivotField
Dim i As Long
Dim rangeName As String

On Error GoTo Errorhandler

If ActiveSheet.Name = "ByYear" Then
rangeName = "Metadata!$B$11"
Set ptMain = Sheets("ByYear").PivotTables("PivotTable1")
ElseIf ActiveSheet.Name = "ByStore" Then
rangeName = "Metadata!$D$11"
Set ptMain = Sheets("ByStore").PivotTables("PivotTable1")
Else
Exit Sub
End If


For Each pfMeasure In ptMain.DataFields
pfMeasure.Orientation = xlHidden
Next

i = 0
Do While Range(rangeName).Offset(i, 0).Value <> ""
ptMain.AddDataField ptMain.PivotFields(Range(rangeName).Offset(i, 0).Value)
i = i + 1
Loop

Exit Sub

Errorhandler:
Debug.Print Now(), Err.Description

End