Change all ranges in the Pivot Table

[Celeda]

Hi,

In first, sorry, I hope not to make a mistake about this forum.

I have 12 files.
I have one sheet which there are 6 pivot tables (TCD).

Every month, I copy paste this sheet in the new month
(in the new file) because I have a new database.

I give a name to the database :Example : File January: Tablo01, ect…

In my 6 pivot tables, I use this name in the first step : Range :
Tablo01.


When I copy paste my sheet (TCD), obviously, I have a link
with my other file (with the name of the previous Database)
Manually, I change every range with the new name
in these 6 pivot tables.

I would like to change by macro, directly, the new name
in the Range :
if Tablo01 for January, it will be Tablo02 for February.

Do you think if it's possible by macro (with inputbox ?)
to change the new name in all the pivot tables.

Thanks a lot for your help.

Have a good day.

Celeda

 

[John James]

Hi Celeda,

I think you should probably NOT be looking to change names each month.


Presumably you have a datatable for January's data. When February'
data arrives, I'd suggest adding it to the January data (or replacin
it) - not building a new table, or a new range name. Depending upo
size, you should be able to hold all of your data (all months) in
single database, and select which month you are choosing to report o
from drop-down lists in your pivottable. This will eliminate the nee
for these file linkages because all data is held together.

If the data becomes too big, then consider holding the data in Acces
(or another database) and extracting what you need into th
pivottable.

I assume the 6 pivottables are built on the same data query (i
possible) rather than being 6 independent pivottables based on si
different data queries. As a rule, the single query is far more memor
efficient and faster without losing functionality.

If you reject this opinion and still want to have a macro to chang
names, then I'd suggest posting in the excel programming group

 

[Celeda]

Hi,

Dear John James,

Non, No, I do not want to reject your opinion!!

It's *very excellent* . It's *a good idea *: (or replacing it)
Effectively, I do not understand why I did not think to do that.

"I assume the 6 pivottables are built on the same data query" : yes, It
will be more efficient to replace the database in January sheet
because my database will be very big at this end.

Thanks, Thank you very much. I appreciate your help and I am very
happy.

Happy easter.

Celeda