Changing data source in pivot table

Hi,

I made two pivot tables from the same data table. One table represents Number of actions and the other the reasons for the actions. I need to add to this report every day.
I add new rows to the bottom of my data sheet, I click on my pivot table, analyze ribbon and select the data cells to change my source. The actions pivot table updates fine (I lose some formatting though), but my PROBLEM is that the "reasons" pivot table does not react the same way. It simply loses all data and becomes weird.

Why doesn't it update the same way? What can I do to make it update more effieciently?

Hi,

By changing the data source for the pivot-table to a dynamic excel table, Excel solved your problem for re-adjusting the data range.

To do that:

1. Convert your dataset on the sheet where your data is located to a Table.
2. Select the dataset range then through the Ribbon: Insert > Table
3. In the Create Table dialog, check "My table has headers" > OK

Click on the Table, then in the Ribbon's "Design" tab, look to see what name Excel has automatically assigned the new Table.
Let's assume it's the first table in the workbook and it's named "Table1".

Go into each of your pivot-table's "Change data source" dialog and replace the current range address with: Table1

Remains the updating of the pivot-table, if i understood your question correctly it would be on a different worksheet.

I’d suggest you use code that will be triggered each time you activate the worksheet containing the pivot table. To cover more than one pivot tables on the worksheet, the code will check if the worksheet contains one or more pivottables and refresh them all.

The code needs to be pasted into the ThisWorkbook code module of your workbook.

Please Login or Register  to view this content.

This should increase efficiency on your daily routine to add data to your sheet.