LinkBack URL
About LinkBacks
Hi,
I want to update all pivot tables in multiple excel files.
For example: I have 10 excel files which contains the same pivot table.
Is it possible to update all of them without open and refresh them one-by-one?
Hi,
I want to update all pivot tables in multiple excel files.
For example: I have 10 excel files which contains the same pivot table.
Is it possible to update all of them without open and refresh them one-by-one?
Hi XlapatsaS,
You can try setting your pivots up by creating the first one, copying it & pasting to the next worksheet & so on.
Test this by making a pivot table, copy it & paste it somewhere next to the original pivot table on the same worksheet & try making a change to the source data such as removing a few Rows of data.
Now go to the original pivot & refresh it, you should see the second Pivot table also refreshes.
Otherwise, you can write a Macro based on the Pivot Table number & tell it to refresh the Pivots.
Remember you are unique, like everyone else
Hi noboffinme,
first of all thanx for your response.
If the pivot tables are on the same excel file (even if they are in multiple sheets) I can refresh them all with the refresh button in data section.
If they are on different files I can't refresh them all even if all the files are open.
The pivot tables are the same cause the excel files were created by copy-paste the entire file and just rename it.
Is it possible to create a macro (if no other way) which refresh all the files and if yes how (i'm not so familiar with macros).
Thanx again!!!!
Sorry I misunderstood,
So these are totally separate Excel Workbooks with Pivots inside them?
Yes, the pivot tables aren't on the same workbook.
It's just the same source.
hi,
I am not sure how to refresh all pivot table, if Pivot tables present in different work books (different files) are open but if these diff pivot table are not open and you need to refresh automatically when you open all pivot table, there is an option to do this.
Right clink on the pivot table, you will find "Pivot table option" dropdown, select it and go to DATA tab and check "auto refresh on opening file" option. you need to do this in each pivot table before closing the file. when you open it you dont need to refresh it each table.
Hi murarihyd,
thanx for your answer.
I know that feature but the db tables are very big so if the pivot table refresh automatically when I open the files it takes to much time.
Thanx for the answer anyway!!!!
See you comment in #3
In that case the datasource of all the files is the same.
The pivot tables are the same cause the excel files were created by copy-paste the entire file and just rename it.
In that case you are better of using 1 file, and add the differant pivot tables in that file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi oeldere,Originally Posted by oeldere
I mentioned it in another comment that the source is the same in all files.
If I understand your comment, you tell me to have one excel workbook and different sheets in it.
It's workable if you have 10 workbooks with 1-2 sheets, but in my case I have 10 (and it'll be over 50 in the near future) with 8-9 sheets each. So I cann't have so many sheets in only one workbook.
In any case thank you for your response.
Then you have to give us more information.
How many rows and columns do you use in each sheet in each file?
Please explain what is the need for that.It's workable if you have 10 workbooks with 1-2 sheets, but in my case I have 10 (and it'll be over 50 in the near future) with 8-9 sheets each. So I cann't have so many sheets in only one workbook.
Or show us (post) 1 excelfile with a couple of sheets, without confidential information.
Hi again,
the amount of rows and columns is differnent from sheet to sheet.
The excel files contains the codelist of all products from super market with the amount of sales of each product.
Each workbook is for one of our store and has one sheet per month. So I have 9 sheets now and I'll have 12 at the end of the year.
It's very difficult and takes a long time to open them one-by-one to refresh.
So I want (if possible) to find a way to refresh them all.
Sorry for my english. It's not my native language.
I try to describe my situation as better as I can.
Thanx for your time.
You English is good (enough; at least for me).
I think you want to show cummulative values per store (you didn't mentioned, what the pivot table is showing).Each workbook is for one of our store and has one sheet per month. So I have 9 sheets now and I'll have 12 at the end of the year.
For this problem, I advice you to make just one sheet and put all month in that sheet.
(this can be done with an macro)
That will make it a lot easier to analyse your data with an pivot table.
(or do you use power pivot; I'm not familiar with that tool).
How many product do you use (50, 500, 5.000, 50.000)?
Each sheet has > 1.000.000 rows.
Sorry for not mentioned it ... yes I want to show comulative values per store.
I want to group this values per sheet by month.
So if I have all of the tables in one workbook at the end of the year I'll have a workbook with many sheets (stores*12).
My final report has to be seperates workboots for each store.
So it's not only difficult to use a worksheet with so many sheets, it's not in the terms of the report too.
The product I use are about 15.000-20.000.
So each sheet has about 15.000 rows.
I think I use pivot table (I don't know what power pivot is ... i insert it into excel by excel insert tab -> pivot table -> insert pivot table).
Since you don't know power pivot, you did not use that tool.
You use the also very powerfull pivot table.
++++++++++++++++++++++++++++++++++++++++
12 month of data (12 * 15.000 = 180.000 rows) will fit in 1 sheet.
Analyzing the data over 1 sheet is much easier than over differant sheets.
So again, I will advice you to add all the data of 1 store in 1 sheet (instead of 12 sheets).
How do the data you get in the excel file each month? (macro or copy / paste or something totaly differant)
Sorry, I lost you here.
Every sheet has one pivot-table in it (I don't copy paste datas, I just copy/paste the entire workbook).
Do you mean how the pivot table was created?
I understand that 12 months of data can fit in 1 sheet and I appreciate your help but the form I use is more readable/printable etc.
So my final format has to be in the format 1 month per sheet and 1 store per workbook (orders above).
Unfortunatelly I cannot change the form ...
You get better help if you post 1 (small) excel file, without confidential information (for 3 sheets).
Please also add the pivot table based on this fictual data in your file.
Hi,
I upload a sample of the file.
The pivot's fields are in Greek.
This is for one store and has the three first months in sheets.
The original file has more items and 3 measurables values from the section Sales(Πωλήσεις), the 3 measurables are Quantity(Ποσ Πωλήσεων) under the section Quantities(Ποσότητες) - Values without tax(Καθ Αξία Πωλήσεων) and value with tax(Αξία Πωλήσεων) under the section values(Αξίες).
The foto I attached is from the original file without the values.
Just to show you how it looks like
So I want to have many of this files and each of them to have 12 sheets.
Thanx
Ok now I see, you get a pivot table for each month.
But where do you have the data stored.
I guess that is in the same file.
If so, 1 show you 2 examples of how I would analyze the data.
See the attached file.
Hi,
sorry for not mentioned it before.
The pivot table I use is sql connection. (The source is a sql BI model).
The format of the the file is just like your first example (see the attached picture), the only difference is that the values are not similar so I don't have the total of rows.
I am not familiar with sql connection.
I can't help you (enough) with that one.
I hope the given examples will give you an idea (though).
Thank you so much for your help and for your time.
It's my fault I didn't mentioned it from the beginning.
Have a nice day.
Glad I could help.
Have a nice day too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks