Hi,

I have a follow up on this, I obtained the VBA on forum and it works on all other pivot tables except the one that I am using, the issue is in feeding the pivot table filter. I cant figure out what would be the appropriate code to refer to this [Calendar].[Year-Month] section. This data is directly fed from ERP hence the query looks "non-standard".

VBA code that works with the exception of what needs correct reference
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("S6:S7")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("SourceData").PivotTables("Prior")
Set Field = pt.PivotFields("[Calendar].[Year-Month])") <= this is the part that causes issue
NewCat = Worksheets("SourceData").Range("S6").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub


Details of pivot query from ERP (this includes all views visible in pivot, the one I need to input to VBA as filter is marked in bold)

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Account].[Ledger Account Name].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Model] WHERE ([View].[View].&[Periodic],[Currency Type].[Currency Type Id].&[HFM],[Legal Company].[Legal Company Id].&[XXXX],[Calendar].[Year-Month].[All],[Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


I have tried the following:
pt.PivotFields("

1) [Calendar].[Year-Month])
2) Hierarchize({DrilldownLevel({[Calendar].[Year-Month].[All])
3) Hierarchize({DrilldownLevel({[Calendar].[Year-Month])
SELECT NON EMPTY Hierarchize({DrilldownLevel([Calendar].[Year-Month])

None of them seem to work and pivot filter does not get updated.