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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,119,120
Messages
5,576,212
Members
412,706
Latest member
msousa25
Top