Refreshing PivotTable Using Macros

[jkuyp]

Hey need some help please....

I'm trying to refresh several pivottables across my excel doc with one macro. I want the refresh to be updated data reflecting the most current date (current date being the most recent end of month). I have the code pretty much mapped out and functioning, however it only works when inputting a date manually into the code, but fails when I try to reference another cell.

I want to be able to reference another cell so that I don't have to manually update my macro to reflect the most current date. Sub 'update_piv3t() is where I'm encountering an error. Please help, I apologize any confusion in my explanation.

link to image of code: https://imgur.com/a/FvZoM

Copy&pasted code:

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 Variant


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

 

[jkuyp]

Update image : https://imgur.com/a/CHX4v