Help with using pivot tables

[ajc623]

I have a sheet that list orders going out for each day of the week and I have added a pivot table to summarize the quantities of each product for a given day. Since this is a template I would like to have a blank sheet with the various data fields for the day and off to the side have the pivot table that would populate as info is entered. The problem I have encountered is when I create the pivot table and then copy the sheet the new sheets pivot table is linked to the original sheet and not the data on the new sheet. I have tried to figure this out but have not and I am hoping I am just missing something obvious. If possible I would like to see if there is a way to refresh the data in all pivot tables through a macro or something else as the data is changing throughout the day. I have a simple version of what I using and would be happy to send or post if needed. Thanks

Andrew

 

[RandyMorris]

1) Create two separate tables using the Pivot Wizard (ALT D P)
2) Use refresh all (CTRL + ALT + F5)
2a) The VBA/macro code is

Activeworkbook.RefreshAll

 

[ajc623]

Randy, thanks for the response. I have the macro working fine but I think I am still missing something on the Pivot table portion. When I go to the pivot wizard I picked Microsoft Excel list or database and then I picked to create a Pivot table. On step #2 I pick the range the data I need is in and in step #3 I picked where to place the pivot table. What I ended up with is what I have had in the past which is a pivot table that summarized the data how I want but everytime I need to create a new sheet for the next day it links to the original worksheet and will not link to the data on the new sheet. In the end I need a sheet for today (072915) with a list of the product shipping and the pivot table to the side with the summary and then be able to to copy that sheet so I have another sheet (073015) with what will be shipping tomorrow and have that sheet have the same pivot table but pulling the data from the new sheet. Let me know if that makes sense.

 

[RandyMorris]

So if I understand you, your pivot table is not dynamically updating with your new data?

A pivot table points to a range that you specify, thus you will need to repoint it to your new data each day. What I would recommend is if the fields are the same, make one large spreadsheet tab with the data, then another tab with the pivot. Then you can pivot by day and just change the range as necessary.

 

[ajc623]

Randy, thanks for the reply. Is there not a way to have the pivot table point to a given range on the same sheet the pivot table is on? It seems to me there should be a way I can create a blank sheet or template with all data fields that will get filled in for a given day and on that sheet also have the pivot table. When I need to create a shipping sheet for a new day, I could copy the template which would be blank fill in the data for the new day and when I refresh the sheet the pivot table would update. I would repeat this process with each new day copying the template and creating a new sheet while keeping the data from the past as well. The problem I see right now is when I copy the sheet it still pulls the data from the previous sheet but the correct range, is there a way to just have the pivot table pull the data from the current sheet?

 

[RandyMorris]

There is a way, but you have to create the pivot table each time to the best of my knowledge as the pivot table is for a specific set of data. To a pivot table, a copied sheet is new data. Re-pointing the table takes 10 seconds.