Filtering Data by Date into New Sheet and Change Data Source of Multiple Pivot Table

[ismaill]

Hello everyone,

I recently start using VBA and Mr.Excel is helping me a lot during this learning process. However there is an issue that I couldn't solve by my own.

I've a Master_DATA sheet, in which all data is comes through a query from OneDrive. And there are several pivot tables in which source is a Table "DATA" in Master_DATE Sheet. All pivots are connected to Pivot Charts. These charts are on Dashboard Sheet.

I need a macro which apply filter to "DATA" Table according to "Exp. Closing Date" column (or column N) to get before today's date, and then the macro should copy filtered data into new sheet which named "DATA_OD". Then, I'd like to make this new sheet as source of all Pivot Tables. So, charts on the dashboard automatically will be updated.

Pivot tables are on 3 different worksheet.

The way I've tried is here:

First, I used this formula on the new sheet ("DATA OD") to filter and copy data :

=FILTER(DATA;DATA[Exp. Closing Date]<TODAY())

Then use below macro:

Sub AdjustPivotDataRange()

  Dim pt As PivotTable, pc As PivotCache
    Dim dataSheet As Worksheet, ws As Worksheet
    Dim startPoint As Range, dataSource As Range, newRange As String

    ' get worksheet with data
    Set dataSheet = ThisWorkbook.Worksheets("DATA_OD")

    ' Dynamically Retrieve Range Address of Data
    Set startPoint = dataSheet.Range("A1")
    Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell))
    newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)

    ' create new PivotCache
    Set pc = ActiveWorkbook.PivotCaches.Create( _
               SourceType:=xlDatabase, _
               SourceData:=newRange)

    ' loop through all tables in all sheets
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables

            ' update pivot source and refresh
            pt.ChangePivotCache pc
            pt.RefreshTable

        Next pt
    Next ws

End Sub

I also need a second macro to turn everything back but I think I can handle that by myself.

Hope the explanation is clear for you. Thanks in advance.