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.
=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?
On a spare sheet, create a column to take your items, then write a loop to iterate through your items and the filename at the save as line is the item you want.
Create a named range called Items that refers to the range holding your items on the INPUT sheet then this should get you started:
Dim Item as Range
For Each Item in Thisworkbook.Worksheets(“INPUT”).Range(“Items”)
‘Your code comes here to process the report then Save As
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