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's data arrives, I'd suggest adding it to the January data (or replacing it) - not building a new table, or a new range name. Depending upon size, you should be able to hold all of your data (all months) in a single database, and select which month you are choosing to report on from drop-down lists in your pivottable. This will eliminate the need for these file linkages because all data is held together.

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

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

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

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

[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