Pivot Table doesn't get new data

[SandsB]

My report has a pivot table that gets data from a named range of 10,000 rows. My data never goes over 5,000 rows but I add to it every day until the end of the month. The pivot table is based on the date within the row and if I "select all" I still don't see the new data. I need to manually click on the new date before it's displayed. I'd prefer a solution that doesn't require Select All because that gives my chart based on the pivot table a Blank in the legend. Is there something I can do to my Pvot table so it'll update itself when new data appears or is there something I can add to a macro?

 

[SandsB]

"The pivot table is based on the date within the row and if I "select all" I still don't see the new data." This should say it doesn't add the new data if I use Select All as the default. I still have to pick the new date individually or deselect/reselect all manually.

 

[Armstrong_N14]

I don't know much about macro but I find the Record Macro in Excel a very helpful one!

Sub ShowallExceptBlanks()

    With ActiveSheet.PivotTables("NameOfYourPivotTable").PivotFields("NameOfYourPivottableField")
        .ClearAllFilters
        .PivotItems("(blank)").Visible = False
    End With

End Sub

 

[Armstrong_N14]

I think Pivot needs refreshing the data every time an entry on the source is made. You can add RefreshAll code just before the 'With' Line on the above code.

Sub ShowallExceptBlanks()

    RefreshAll
    With ActiveSheet.PivotTables("NameOfYourPivotTable").PivotFields("NameOfYourPivottableField")
        .ClearAllFilters
        .PivotItems("(blank)").Visible = False
    End With
End Sub

 

[RoryA]

If I understand you correctly, you need to right-click the relevant field, choose field settings, then check the 'include new items in manual filter' option.

I'd also suggest you use a table rather than a range that includes lots of blank rows.