Macro to Explode Pivot table by Filter Field Items

Refer to the previous post. We saw how Filter fields can be exploded into individual sheets and files. In this article, I am releasing a macro – which does all the hard work for you. Of course, you must use it at your own risk. The source code is also included for you to view, learn from and modify. Created by Raj Chaudhuri and myself.

Photo credit: Johnson Cameraface / Foter / CC BY-NC-SA

Download the macro file

Download the ZIP FILE.

Unzip the file PivotFilterExplode Macro.XLSM and save it to a local folder.

This file contains the macro with source code. It also contains a sample pivot table which you can use to test the macro. You must enable macros to run it. Usually, you should see a message asking you to enable macros when you open this file. In newer version of Excel, there will be a yellow bar just below ribbon with an Enable Content button.

The macro: PivotFilterExplode

Keep this file open. You will need to enable macros.

This macro does what we saw in the previous article.

Now you open your file containing the Pivot Table with some field in filter area which you want to explode.

In this example, we have 6 items in the Country field.

Just select the desired filter field. Then run the macro.

To run the macro, open the View Tab in the ribbon – choose Macros. The macros dialog will appear. Open the Macros in dropdown and choose All Open Workbooks or better still choose the PivotFilterExplode Macro file.

Now select the macro PivotFilterExplode and choose RUN button.

A dialog will appear informing you about the field being exploded and the number of new files which will be created. Choose Yes to continue.

Now wait till you see another dialog. This dialog will inform you about how many files were created. There will be a lot of screen activity. I could have suppressed it, but I chose to keep it so that you can see that something is happening.

If the pivot tables are large and if there are many fields, this may take a long time. If you think nothing is happening – just kill it.

Here is one of the Country based pivot table which got created (out of 6). Notice that the filter area contains only India data now.

What does the macro do?

Assuming you have clicked inside one of the filter fields, the macro will run. Otherwise, it will just show an error message and quit. If it runs, this is what will happen.

1. The macro will filter the pivot table for each item in the selected filter field
2. Each filtered pivot table will be created in a new Excel file (workbook)
3. Each workbook will have the pivot table filtered on that particular item and another sheet containing raw data for that pivot table
4. The newly created files will not be saved. This is because usually, you just want to mail each of these files to relevant people. The original pivot table file is not disturbed in any way. So you can always created the filtered view anytime. That is why there is no need to save the newly created filtered files.
5. Of course, if you want, you can save those files as well.

Precautions to be taken

This macro does not change any data in the base file. It does NOT save the base file. If the macro crashes, you will lose all unsaved changes.

So ensuring that the base file is safely saved is your responsibility. Ideally, just make a copy and run the macro on the copy. That is the safest approach.

Ideally, keep only the pivot table file and the macro file open. Close other files to prevent inadvertent side effects and damage to unrelated files due to crashes or any other errors.

Bugs and suggestions

Post them as comments here. I will try to address them as time permits. Let me know if you find it useful as well. Share it with colleagues and friends if you like it.

Do you want me to include code to mail each created file to specific persons?

If many readers request it, I will create a version 2 of this macro which will pickup the filter items and corresponding email ids and finish the mailing as well (assuming an Outlook profile preconfigured). Let me know if you want it.

Enjoy