Date Change Multiple Pivot Tables OLAP VBA

Felix482

New Member
Joined
Sep 9, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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

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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,118,411
Messages
5,571,947
Members
412,428
Latest member
erino
Top