Hi,

I need to have a series of custom tables, specifically formatted in MS Excel.

I decided I'll make them based on Pivot Table as a source data and Excel links with GETPIVOTDATA do deliver the data to the proper MS Excel format worksheet template. After filling with dynamic data from pivots the template is saved as workbook needed.

Each table consist of around 100 of GETPIVOTDATA links. Each link has it own pivot table in a 'working' worksheet. All the pivot tables are based on the same external MS Excel file economizing on resources. While making the pivot table I decided that the external data is returned to Excel file.

I use VBA to manage thos Pivot tables. As a result the pages of the pivot tables change delivering different results in Pivot table.

Unfortunately I have faced two difficult situations:

1) Data that I use seems to remember with the quite 'statically'. Yes, its true that when I update the MS Excel source file and give refresh on the pivot table, the pivot refreshes. But if I copy the folder both with source and report files the new, copied report file will still source from the original source file. To change it, today I have to manually change the source for each pivot table that is quite time-consuming. Unfortunately I do not know how to make those links more dynamic (always for example take from source.xls in the same folder) or update the source automatically with VBA.

I'd like to make it automatically as manual work always causes more unexpected errors and its quite unefficient. Do you know how to solve this?

2) When I change the pivot with VBA macro, the pivot itself changes, but the links in custom formatted tables on other worksheet do not 'refresh'. I made a workaround by preparing special VBA procedure that after VBA pivot table update, overwrites the links with identical formulas in all cells. (Similar result to selecting the cell manually, selecting the formula and pressing Enter). After that the links are 'refreshed' with the proper, chagned data.

This issue is done automatically, but maybe some of you know more efficient / elegant solution?

My office is 2003 Professional, my system is WinXP.

Thanks,

Michal