Copy a sheet with a Pivot table

[ajc623]

I have a workbook that gets updated daily and for each day we create a new sheet. I have a pivot table that summarizes column D (item#) and column C (quantity). when I copy the sheet the pivot table stays linked to the data on the original sheet and not the new sheet. I must be missing something simple but I can see what it is, using excel 2010. Any ideas? Thanks

Andrew

 

[RudiS]

Am I understand that you correctly that you want to copy and paste a Pivot Table (structure and formatting) but disconnection it from the source?

 

[RudiS]

If my assumption is correct, do the following;


Do not copy or duplicate the sheet (using the sheet tab), rather...


1. Select the cells that contain the pivot table
2. Copy using any method
3. Switch to the destination sheet
4. On the Home Ribbon, click on the dialog launcher (in the bottom right corner) of the clipboard group of buttons
5. This will launch the clipboard pane on the left edge of your screen
6. Click on the topmost clipboard entry representing your last copy
7. The pivot table will be pasted into the destination sheet as a representation of the pivot table (disassociated from its source)

 

[SpillerBD]

I think the OP wants to have the Pivot Table associated with the new data but keep all the formatting. I would say that is better done with VBA.
However, after copying the Pivot Table Sheet to the other WorkBook, you can change the data source.
PIVOTTABLE TOOLS/ANALYZE/CHANGE DATA SOURCE
Most the other formatting remains. If the data structure and column headers are exactly the same, the Pivot Table should merely update to the new data.

 

[ajc623]

Thanks for the response. I do not think I explained what I am trying to do very well. I have a sheet that lists the orders that are going to be shipped for the day. In column C is the numbers of units of each product in the order and column D has the product number. For a given day we could ship several orders that contain the same product so the pivot table that I have just adds up the quantity in column C for each product shipping that day. Since the orders are entered in a template with other information I am trying to make it so when the person creating the sheet for each day can simply create a new sheet for the next day or the next 5 days quickly and have each sheet already have the pivot table there that will summarize the quantity per item for that day. Thanks

Andrew

 

[RudiS]

In my opinion, that would just cost a quick once off filter on the date field.
One can copy the sheet with the pivot table; it will remain connected to the source data, but to review the records for that given day, one can simply apply a filter for TODAY in the date field (assuming this field is added to the pivot table). Remember that a date field can be filtered via a dynamic date filter by using the filter drop down, choosing Date Filters and selecting Today.

 

[SpillerBD]

Thanks for the response. I do not think I explained what I am trying to do very well. I have a sheet that lists the orders that are going to be shipped for the day. In column C is the numbers of units of each product in the order and column D has the product number. For a given day we could ship several orders that contain the same product so the pivot table that I have just adds up the quantity in column C for each product shipping that day. Since the orders are entered in a template with other information I am trying to make it so when the person creating the sheet for each day can simply create a new sheet for the next day or the next 5 days quickly and have each sheet already have the pivot table there that will summarize the quantity per item for that day. Thanks

Andrew

So what you really want is a Template that already has the Pivot table built in it. Then when user "creates" the sheet and adds data, the pivot table is ready.
If the area where the data is input can be set as a Table, that would make the dynamic range for the Pivot Table a little easier.
Otherwise I would set up a macro for the Pivot Table creation and make that part of your template.