Need pivot tabel data source change autometically

khan_safi773

New Member
Joined
Oct 28, 2013
Messages
24
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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

New Member
Joined
Oct 28, 2013
Messages
24
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
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
(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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,477
Messages
5,601,893
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top