Need pivot tabel data source change autometically

[khan_safi773]

Dear All,
Good evening

I am facing a problems need your help.

My problem,
I have a worksheet and it contain 10 data sheet, 5pivot table( one pivot sheet have 4 provost also),
When I copy this worksheet and change worksheet name and past new data. I saw pivot table data source not chane, it still contain old worksheet data.


Note. I can change data source manually, but i need it automaticaly, when i change copy and change name.

It looks when I go manually 'D:\Financial year\Costing\[12005.xlsx]GYI'!$B$4:$U$8000

 

[SpillerBD]

It sounds like you are copying a Pivot Table to maintain the formatting of the Table, and copying it to a new Workbook to try and associate it with that Workbook's data.
When you copy a Pivot Table you copy the Table and the Pivot Cache (where the data actually comes from.)
If it is the formatting strategy, it would be better to use a macro to create your Pivot Table(s) to maintain consistency.
Other alternative may be to have macro that uses current file name and table references to update the pivot cache.

To set for "automatically" change the pivot cache to the new Workbook would rely on an event procedure and run into the possibility of it updating when not necessary. That would interfere with actually working in the file and consume resources unnecessarily.

 

[khan_safi773]

Dear SpillerBD,
Thanks for your reply.

Anyway, i did find my soliution from your reply, I do not want to make huadge pivot table for same data reconcilation , I set some reconciliation through pivot table from different sheet, so. It is easy for me if I copy hole work sheet and past new data in data file.

I need my pivot table updated data from current sheet.

It can be by using VBA or normal excel formula or function

WBR


Safi

 

[khan_safi773]

Dear all,
I did not get my answer from reply, can any one help me.


Safi

 

[SpillerBD]

(I have not constructed an Event handler but that is what you want)
I believe an event handler that is triggered by changes to a Table (set your source data in a Table rather than a regular range).
You could also use a timer event that updates the Pivot cache every x minutes. Obviously your Pivot Table should be set to update when opened.

From what I read of your original post, I would set up a template with macro's to import the necessary data and then create the desired Pivot table from that data. The Pivot Table can use externally sourced data too, if it is already in an appropriate layout.