Update Pivot Table using Date Ranges outside the Pivot Table

[jasonwhite826]

Does anyone know how to update a Pivot Table's Date column by using a Start Date and End Date Field outside the Pivot Table? I have a Pivot Table that Starts in Cell B5. I have a Start Date that is in cell C2 and an End Date that is in cell C3. I created a Text Box that is the Refresh button. So ideally I know put a Start Date and an End Date in cells C2 & C3 respectively, and hit refresh and the Pivot table would show me data for just those dates I put in Start Date and End Date. The column in Pivot would be called Date. I think it would be a Macro but Im not sure what I would do. Anyone have any suggestions or know what the VBA would be? Any other ideas would be great. Im need this for Excel 2007. Thanks.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Yes, you would need to use VBA to do this with Excel 2007.

The VBA code would depend on which area of the PivotTable the Date field is placed (Report Filters, Row Labels, or Column Labels).

If you reply with which area, I'll provide an example.

 

[jasonwhite826]

Thank you so much. It is in the field list dates running down the side beginning at cell C6. Let me know if you need anything else. I have a example spreadsheet but I don't see a way to upload it or copy and paste an image. If there is a way to do that and you need to see the spreadsheet please let me know. Thanks!

 

[Jerry Sullivan]

Here's some code that you can try. Assign this macro to your Textbox "button".

Sub PivotFilterByDates()
'--Filters RowField or ColumnField to show dates between
'     two dates read from cells.


    Dim dtStart As Date, dtEnd As Date

    
    With ActiveSheet
        dtStart = Range("C2")
        dtEnd = Range("C3")

        
        With .PivotTables("PivotTable1").PivotFields("Date")
            .ClearAllFilters
            .PivotFilters.Add Type:=xlDateBetween, _
                Value1:=dtStart, Value2:=dtEnd
        End With
    End With
End Sub

 

[jasonwhite826]

I get a Error that just says 400. I will say I am doing this in Excel 2013 but the other person that will be using this has Excel 2007. Im not sure if that is the reason for the error. Thanks again for the help.

 

[Jerry Sullivan]

You'll need to modify "PivotTable1" to match the name of your PivotTable.
The code also assumes your field is named "Date"

If aligning those two names doesn't fix it, please identify what line of code the debugger is stopping at when the error occurs.

 

[jasonwhite826]

This worked perfect! Thank you so much!

 

[jasonwhite826]

I had a quick question on this. What if instead of the Date field being a column in the pivot it was a filter in the filter section of a Pivot Table. Would that change the VBA at all? I moved the Date field from Column to Filter and now it says error 400. Is that possible? Thanks again!

 

[Jerry Sullivan]

Yes, at least as of xl2010, the Report Filters don't allow the use of the .PivotFilters.Add method, so the VBA code needs to step through each PivotItem in of a date field in the Report Filters area and set Visible to True or False.

Here's an example....

http://www.mrexcel.com/forum/excel-...g-visual-basic-applications-excel-2010-a.html