Looking to split up data and pivot resulting data set in one function

[rfaz]

Hello,

I am currently having an issue with my macro and I'm wondering if anyone on this could assist me.

The aim is to separate a set of data by country into different workbooks, include a pivot table of the data and save each workbook individually.

I have written two separate macros which work independently but when I try to combine the two macros the pivot part does not work.

The issue appears to be that the pivot part of the macro does not run in the newly created workbook but in the original unfiltered workbook.

I have attached the 2 separate macros, combined macro and sample data.

Any help would be greatly appreciated

RFAZ

[dflak]

I presume you don't want the people in one country to see the data for people in another country.

[rfaz]

I presume you don't want the people in one country to see the data for people in another country.

Correct. A separate file will be sent to each country which will contain a pivot table in one tab and the raw data for that country in the other

[dflak]

This requirement is handled by pivot tables and named ranges that overlay the pivot tables. For information on named ranges, see this article:

There is a pivot table in Column I. All I use this for is to get a unique list of names for the countries and I give the results a name “Country_List.”

The pivot table in Columns K:P is a dump filtered for the specific country on the list. This pivot table has, in the background, data for the other countries as well.

So, I overlay the results of this pivot table and give it a name: “Pivot_Range.”

I use pivot name as a data source for the pivot table in Columns R:W. This pivot table looks the same as the one in the previous columns, but it does not have other country data in the background. I overlay the results of this pivot table with the name: “Pivot_Copy.”

It is this range that I copy to the target worksheets.

So, I go down the list in column I, cell by cell, set the pivot table filter in the first pivot table refresh the pivot tables and copy the range to the target workbook.