How to Pivot tables together by combining 5 source files

100 views
0 Comments

Hi all,

Would like to ask advise from your expertise. Appreciate if you can give some advise.

I have created a Power pivot by combining 5 source files. The source file need to be update every month.

From the power pivot,
1) I use the combined data to do pivot table to enable me to filter according to the item I want.
2) Then, I use macro to open, refresh, filter the item I want, paste value (as each file must only include the item I filtered) and save it as a new file by the name of the item I want. For example, I filter the item \’ABC\’, so I save the file name as ABC.
Note*
=I have 34 items to filter and save
= save as the item name as I need it to be separate report as each item is individual.

I found that it is be time consuming to use the macro to redo the step again.

Is there any other way to make it more time saving?

Changed status to publish
0
Chan (anonymous) 0 Comments

You can try to use the feature generate report filter in pivot table to generate each filtered item to a sheet and then use macro to save each sheet as a new file

Changed status to publish
You are viewing 1 out of 1 answers, click here to view all answers.
Write your answer.