Hello Excel gurus,
There are several examples of this task as long as it is a simple pivot table based on one table.
I am using multiple tables with power pivot. The filter field in this pivot table comes from a different table.
I have used the code below. It shows Type mismatch error# 13, no matter what changes I make to the data.
The error shows the "Set Field =" line.
TblDateMast simply has the dates with day numbers, month, quarters, and years columns.
The other revenue-related information comes from the TblRev table.
The pivot table and the cell value to filter the pivot table are in different worksheets.
I am trying to filter years.
Thank you in advance.
There are several examples of this task as long as it is a simple pivot table based on one table.
I am using multiple tables with power pivot. The filter field in this pivot table comes from a different table.
I have used the code below. It shows Type mismatch error# 13, no matter what changes I make to the data.
VBA Code:
Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As Range)
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As Long
NewCat = Worksheets(7).Range("B9").Value
Set pt = Worksheets(8).PivotTables("PTYOYSLLastYear")
Set Field = pt.PivotFields("[TblDateMast].[Date_Mast (Year)].[Date_Mast (Year)]").VisibleItemsList = _
("[TblDateMast].[Date_Mast (Year)]." & NewCat)
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End SubThe error shows the "Set Field =" line.
TblDateMast simply has the dates with day numbers, month, quarters, and years columns.
The other revenue-related information comes from the TblRev table.
The pivot table and the cell value to filter the pivot table are in different worksheets.
I am trying to filter years.
Thank you in advance.
