VBA Update to Multiple Pivot Tables

[Hokie502]

Hello. New member. I had a situation with a dashboard (Excel 2013) that consists of various tables, charts, and text. We been asked to expand the backend data and allow a user to choose their own dates. At first I thought this would be easy by way of Slicers and the Timeline function. However, I've since discovered that will only affect multiple PivotTables if they are derived from the same source.

To setup my own solution I pulled together some VBA to adjust date ranges in the source tables. That worked, but I soon realize that won't adjust the associated Pivot tables.

Now I'm trying to get VBA to instead adjust the pivot table date ranges in the FILTER area. I created a sample workbook with the following code that worked:

Sub Button1_Click()
Dim pt As PivotTable
Set pt = Sheets("Sheet1").PivotTables("PivotTable1")
pt.PivotFields("Date").ClearAllFilters
pt.PivotFields("Date").CurrentPage = Range("B2").Value

End Sub

I've tried to apply this to a copy of the source workbook without success. I've even tried pulling source workbook data to a new workbook with no success.
When I apply the my workbook, I receive the following with the rror on the last row.

Run-time error '1004': Application-defined or object-defined error

I thought it might be a date formatting issue, and adjusted accordingly, but to no avail.

I conducted some more research this morning and found some useful posts by Jerry Sullivan, but couldn't get any of those to work either.

Pivot table with data validation
Update Pivot Table using Date Ranges outside the Pivot Table
VBA help to update pivottable date filter with a single cell value date

 

[Roger Govier]

Hi

I think I would be inclined to set up an Advanced Filter, where the user could enter the Start Date and End date of the data they wanted to see.
Then use Event code when both of these values had been change, to extract data from the Source to a new Table.
Make this new table the Source for all of your reports.

The code to do the extract is trivial and runs very fast. An example is posted below

Sub FilterCF()


    Range("tblAll[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Alldata2").Range("AB1:AAC2"), CopyToRange:=Sheets("Alldata2").Range("AA5:AS5"), Unique:=False
        


End Sub

In this example the data is on a sheet called Alldata2, and the source data is in a Table called tblALL
The start data is entered in AB2 and the End date in AB3 and all data between those dates is extracted to columns AA:AS of sheets Alldata2
The data in AA5:ASxxx is the new Table that is passed to the PT.

 

[Hokie502]

Hi

I think I would be inclined to set up an Advanced Filter, where the user could enter the Start Date and End date of the data they wanted to see.
Then use Event code when both of these values had been change, to extract data from the Source to a new Table.
Make this new table the Source for all of your reports.

The code to do the extract is trivial and runs very fast. An example is posted below

Sub FilterCF()


    Range("tblAll[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Alldata2").Range("AB1:AAC2"), CopyToRange:=Sheets("Alldata2").Range("AA5:AS5"), Unique:=False
        


End Sub

In this example the data is on a sheet called Alldata2, and the source data is in a Table called tblALL
The start data is entered in AB2 and the End date in AB3 and all data between those dates is extracted to columns AA:AS of sheets Alldata2
The data in AA5:ASxxx is the new Table that is passed to the PT.

If I understand you correctly your suggesting applying a two date values, and then instructing Excel to extra records from each source that align between those dates, and appending them to a new table. The values in the new table will be used to generate one or more pivot tables.

I have 11 different sources of data. Some are tables connected to SharePoint, while others are tables we manually update. Also, field names are not the same across the sources for the same value (such as date). You think you're solution would still work in that scenario?

 

[Hokie502]

I used the record macro function to filter between two dates that I selected, which resulted in the following with Date1 and 2 being the respective dates I selected:

 ActiveChart.PivotLayout.PivotTable.PivotFields("Start").PivotFilters.Add2 Type _
        :=xlDateBetween, Value1:=Date1, Value2:=Date2

Since I want to have a user set the dates, I found an approach and tried the following code:

   Dim lngStart As Long, lngEnd As Long
    lngStart = Range("B1").Value 'assume this is the start date
    lngEnd = Range("B2").Value 'assume this is the end date
    
    ActiveChart.PivotLayout.PivotTable.PivotFields("Start").PivotFilters.Add2 Type _
        :=xlDateBetween, Value1:="lngStart, Value2:="lngEnd"

but I receive an error of:

Run-time error '91': Object variable or With block variable not set.