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.

Stepan1010 · Accepted Answer · 2012-10-17 18:23:00Z

1

Just refresh your pivot table:

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

Good Luck.

answered Oct 17, 2012 at 18:23

Stepan1010

3,1341 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chronocidal · Accepted Answer · 2018-02-12 12:11:29Z

0

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)

answered Feb 12, 2018 at 12:11

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Collectives™ on Stack Overflow

Macro using Dynamic Pivot table values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related