Loop through selected pivot table filters using a dynamic range on a worksheet

  • Hi folks

    I hope I can explain reasonably well what I'm trying to do here.

    I have a worksheet which contains a pivot table which has a number of fields set up, for example one of these is called "Name". I need to run a macro which will loop through each of the various values of "Name" (there are about 50) and if that particular value of Name matches a list of unique occurances of Name in a dynanic named range in another sheet in the same workbook then I need to filter by that Name only in the pivot table then create a new workbook and paste special values the filtered extract of that pivot table into a sheet into the new workbook, save it and then close the new workbook, before moving on to the next value of "Name".

    I am ok with the copying and pasting into a new workbook part because I already have some code that is working ok for this part. What I am struggling with and would appreciate some help with is the bit where there is a variable list / range of "Names" in a sheet and using that list to iterate through the pivot table, filter the relevant names one by one seperately only if the name matches what is on the range (ignoring any names on the pivot that are not on the range) and then moving on to the next value of Name in the pivot and then checking if it is the range etc etc. I think it should be a kind of for next loop or case statement with an array contained values in the range, being checked against the values in the pivot table Name field but I just can't translate my thoughts into the code.

    I hope I have explained this well enough of course I can provide some clarification if necessary. Any help would be great.

  • Re: Loop through selected pivot table filters using a dynamic range on a worksheet


    Sorry for the delay. I've attached an xlsb which hopefully illustrates what I'm trying to achieve. It's simplified but I can hopefully adapt any solution to my more complex real life version.

    There are 3 sheets, data, lookup and pivot. The data tab is the source for the pivot table. In the pivot table there are various names, eg Alana, Boris, Cojak etc etc. The lookup tab is a list of names, all the names in that list will always be in the Name column in the pivot tab. But not all Names in the Pivot tab will be in the list of names in the lookup sheet.

    What I'd like is to run through the list of names in the lookup tab one by one. Filter tha pivot table by that name, copy the output and paste special values onto a new tab (and save as new file and exit that new file but I can sort that part) then move on to the next name in the list in the lookup tab.

    So the first name in the lookup tab is Boris. So filter the pivot table on Name = Boris, copy and paste special values in new tab. (save as new file then exit the new file). Next name in lookup list is Alana. Change filter in pivot table to Alana. Copy and paste special values in new tab. etc etc

    In real life there is a lot more data and a lot more columns, names etc but if I can get something that works on this small sample I can adapt it to the real life version. I hope that anyone can help...


  • Re: Loop through selected pivot table filters using a dynamic range on a worksheet

    You could try recording a macro doing that to start you off. This is what I came up with after a bit of tidying up and some googling. I haven't done the saving to a new file.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!