Filter Pivot Table 'Report Filter' based on Cell Range (Select Multiple Items)

[Nylacast]

New to asking Excel questions here, I hope someone can help me!?

I have 6 Pivot Tables (PivotTable1, 2, 3 etc.) in the Worksheet "Downtime_data".

Want to be able to filter my dates to 7 days based on any give start date. For example: 09/05/2018 is entered in to A1 within "Downtime_data". So RANGE A1 to A7 it shows -

A1 - 09/05/2018

A2 - 10/05/2018

A3 - 11/05/2018

A4 - 12/05/2018

A5 - 13/05/2018

A6 - 14/05/2018

A7 - 15/05/2018

I would like the Pivot Tables (1 to 7) to filter the reports based upon the dates within that given range for A1:A7.

.

Within my Pivot table field lists, I have;

Report Filter = Date (this is what I need to change based on A1:A7)

Row labels = Department

Values = Sum of total

.

If there are no values for that date then it just shows what is actually available.

I do hope someone can help me, I just cannot seem to get it working for a range!

 

[Chihiro]

What you want will likely need VBA.

Have a read of...
https://chandoo.org/forum/threads/c...n-a-range-of-cell-with-vba.37294/#post-224151
https://chandoo.org/forum/threads/updating-pivot-table-filter-with-date-range-vba.30954/

And see if you can adopt the code.

If not, upload sample workbook with enough data to demonstrate your issue and clear indication of what your end result should look like.

 

[Nylacast]

Hi Chihiro,

Thank you for the examples - I am looking to use the PivotTables example for getting it to work as it seems to be the easiest to understand for me.

I have posted the information like you have in the example 'Broker Test'.

However, I am getting a error each time I run with:

Sub DateFilter()

'Set the Variables to be used
Dim pt As PivotTable

'Amend here to filter your data
Set pt = Worksheets("PivotTables").PivotTables("PivotTable1")
    pt.PivotFields("Date").ClearAllFilters 'This line is needed to clear existing filter before applying new one
  pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=CLng((Range("SDate").Value)), Value2:=CLng((Range("EDate").Value))

It's the bit below here which flags up every time -
End Sub

  pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=CLng((Range("SDate").Value)), Value2:=CLng((Range("EDate").Value))

Error message is :

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


I have added the names ranges thinking that was the cause but it's not.

Hope you can help? Thank you

 

[Chihiro]

Can you upload desensitized sample? It would make troubleshooting so much easier.

The error message indicates, you don't have one of the object used in the code present in the workbook.

 

[Nylacast]

Hello Chihiro,

I have added an edited version, however you can see where I'm getting the data from and how the Pivot table looks...

Thank you for your continued support.

Regards,

 

[Nylacast]

I've just been looking at the type of filter used upon the Date drop-down within the Pivot table.

Mine says 'Label filter' whereas your's says 'Date filter'.

Could that be this issue?
If so how can I sort this??

All of the information upon the Tracker is pulled through from another spreadsheet, and isn't displayed like values in the copy I have attached.

Regards,

 

[Chihiro]

Yes. That would be an issue.

You have PivotTable Data Source set to...
Tracker!$B$6:$V$10000

This will cause your pivot table to interpret your column data type as "Text" and not as "Date" field. Since you have more text type cell than date type cells in your data source range.

I'd recommend either...
1. Use dynamic named range
2. Excel Table as data range
3. If able, use PowerQuery

Attached, is version using dynamic named range as PivotTable Data Source.