Extract data to create a pivot table/chart

I have data as shown in my sample data file where I would like to eventually
create a graph.

The first step would be to extract the data from selected Stores.

The next step would be to sum the data for each category (Beans, Apples)
combining the data for all stores for each date.

It is probably possible to combine these two steps into one.

Once the data is looking like the data I have in Col M:P
I would like to create a bar graph.

The data will be updated daily and so I would like the table/pivot table
to update automatically/dynamically if possible.

The Store name on the graph could be simplified to 'StoreTotals'.

I hope with the sample file, this will be clear.

Any guidance would be greatly appreciated.

P.S. There are more than 100,000 rows of data and more than 200 'Stores'.

Is this the right type of code to take care of the refreshing?

Please Login or Register  to view this content.

You only need 1 line: ActiveWorkbook.RefreshAll

Originally Posted by josephteh

You only need 1 line: ActiveWorkbook.RefreshAll

Thanks Joseph, I will try it out.

Well my first line above only worked one-time.

I tried Joseph's line above but it didn't refresh my pivot table.

Any other suggestions?

Sorry, I have limited VBA knowledge. Let the forum experts answer you.

Originally Posted by ChrisXcel

So does clearing the data sheet have an effect on the named data range?
Will the named data range be also cleared?

If so, what is the solution to maintaining a named data range, and
at the same time clearing that same data each time a macro is run?

Well the above was the specific problem that was preventing my macro from working.

Most people probably maintain a single database (sheet) and simply add data to the bottom of
the sheet as it becomes available.

In my case, each day the sheet needs to be cleared and replaced by new data.
This is because data is added throughout the sheet, not just to the bottom.

What I was doing was creating a new Table name with the new data.
But I didn't know that when the data was cleared each day and I tried to create
this new Table name, I would get an error (trying to have one table on top of another).

So what I ended up doing yesterday was writing macro code to create a New Table,
name it and then refresh the pivot table.

I know this is just one of many ways to address my problem.

I also thought of possibly copying my new data into my NewData sheet, replacing
all my previous data, except for the headers.
That way I could maintain my Table name (which is used with my Pivot Table).
But it would have involved coding that was too tricky for me.

Anyway, currently all is working well.