PivotTable Data Type Error

[jkuyp]

Hey everyone....

I'm looking to create a macro that will update a PivotTable filter to the most current/recent date -- this date will be end of month (so a 01-31 for example, these dates are formatted like -- 2018-01-31T00:00:00). The idea is that the code will reference a cell within my worksheet and apply this cell's value to the PivotTable filter automatically upon the macro's activation. I have constructed a functioning macro that works in being able to complete the above needs, but I am stuck on the ability of the code being able to reference the cell that contains the my desired filter value -- right now I have only been able to manually insert the date into the code, this works, but is not ideal. As you can see from the link I am receiving this error and I don't know how to remedy this problem. The above subs are functioning, but like I said must be manually inputted with data.

Thanks!




Copy & Pasted:

Sub call_on_pivrefresh_troubleshoot()


Call update_piv1t
Call update_piv2t
Call update_piv3t


End Sub


Sub update_piv1t()


Dim pf As PivotField


Set pf = Sheet3.PivotTables("pivot1").PivotFields("[Asof Dt].[Asof Dt].[Asof Dt]")


pf.ClearAllFilters 'clear out any previous filtering


pf.VisibleItemsList = Array( _
"[Asof Dt].[Asof Dt].&[2018-01-31T00:00:00]")

End Sub


Sub update_piv2t()


Dim pf As PivotField


Set pf = Sheet5.PivotTables("pivot2").PivotFields("[Asof Dt].[Asof Dt].[Asof Dt]")


pf.ClearAllFilters 'clear out any previous filtering


pf.VisibleItemsList = Array( _
"[Asof Dt].[Asof Dt].&[2018-01-31T00:00:00]")

End Sub


Sub update_piv3t()


Dim pf As PivotField
Dim test As String


Set pf = Sheet7.PivotTables("pivot3").PivotFields("[Asof Dt].[Asof Dt].[Asof Dt]")
test = Sheet1.Range("N4").Value


pf.ClearAllFilters 'clear out any previous filtering


pf.VisibleItemsList = Array( _
"[Asof Dt].[Asof Dt].&[" & (test) & "]")

End Sub