Hi,
I am trying to change the date of multiple pivot tables that are from the same OLAP server. I am a beginner and am unsure of how to modify the recorded macro below to reflect what I need.
I would need to select either one date or a range of dates either with a slicer or cells that the date can be manually added or selcted from a drop down menu.
The code is below
Thanks!
I am trying to change the date of multiple pivot tables that are from the same OLAP server. I am a beginner and am unsure of how to modify the recorded macro below to reflect what I need.
I would need to select either one date or a range of dates either with a slicer or cells that the date can be manually added or selcted from a drop down menu.
The code is below
VBA Code:
Sub RAW_DATE_REF()
'
' RAW_DATE_REF Macro
'
ActiveSheet.PivotTables("Same_Day_Rx_Orders_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Same_Day_Rx_Orders_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Same_Day_Rx_Orders_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
Sheets("Truckroll").Select
ActiveSheet.PivotTables("DTH_WHI_BSH_Summary").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("DTH_WHI_BSH_Summary").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("DTH_WHI_BSH_Summary").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
ActiveSheet.PivotTables("TR_RESULTS_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("TR_RESULTS_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("TR_RESULTS_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
ActiveSheet.PivotTables("TR_RESULTS_BSH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("TR_RESULTS_BSH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("TR_RESULTS_BSH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
ActiveSheet.PivotTables("TR_RESULTS_DTH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("TR_RESULTS_DTH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("TR_RESULTS_DTH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
ActiveSheet.PivotTables("DET_INFO_BSH_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("DET_INFO_BSH_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("DET_INFO_BSH_WHI").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
ActiveSheet.PivotTables("DET_INFO_DTH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("DET_INFO_DTH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("DET_INFO_DTH").PivotFields( _
"[ReportDate].[ReportDate Y-M-D].[Report Date]").VisibleItemsList = Array( _
"[ReportDate].[ReportDate Y-M-D].[Report Date].&[2020-08-20T00:00:00]")
End Sub
Thanks!