Macro using Dynamic Pivot table values

Question

I get a lot of data every day with one of the columns being the name of stores. These stores are divided into two regions, say north and south. So , I have a superset with store names including both storesN and storesS. Everyday I get reports from a subset of these names. I use Pivot tables to display the data.

I want to write a macro which will show me just the data from storesN or storesS. If all the stores reported everyday, I could write a fixed macro. But every day the stores that report changes. So is there a way, I could write a macro, which will automatically detect and which names are present and adjust accordingly.

I am using With PivotTables("PivotTable30").PivotFields("Name") .PivotItems("Store1").Visible=False

The names are always a subset of the superset. I hope the question is clear.

Answer 1

Just refresh your pivot table:

ActiveSheet.PivotTables("PivotTable30").PivotCache.Refresh

Good Luck.

Answer 2

If you are deleting all the old rows, just make sure that "Show items with no data" is turned off in the Field settings. Then refresh the table.

If you are only replacing the updated rows, then the easiest solution is probably to just add a "last updated" column and have that filtered for "today" (Or PivotFilters.add2 Type:=xlDateToday if you build your table via code)