Print selection for each PivotItem

Hi all,

I'm writing a script that prints a specific selection of a pivot table that can be filtered for each day of the week (so Monday, Tuesday, Wednesday etc).

The pivot table has a row called "Dayname" which contains each day of the week. The script uses the range provided in cells J2 and K2.

I've looked around and came up with this script but it is not filtering the pivot table for each day to then print the selection.

Does anyone know how I can best do this?

Please Login or Register  to view this content.

I am not sure what you are trying to print. Are you trying to print everything from row 6 cols A:H on down to last item in the pivot table?

In that case, you might look at the following range Table.TableRange1.

Take a look at this reference: http://peltiertech.com/referencing-p...ranges-in-vba/

I understand you want to set each day and then print it.

One issue with multiple selections with pivot table filters is that you must always have at least one item selected or you will get an error. For example if the only item selected is item 3 and you intend on turning on item 4 and you code attempts to turn off item 3 before turning anything back on, you will get an error.

So you have to do a double loop. First you have to turn everything on. Then you have to go back and turn off all those things you don't want to see.

In the code, I defined PvI as a PivotItem just to keep from having to type too much. I also introduced two more variables iI which is the item index - there are 8 days: Mon - Sun + (Blank) and they can be pointed to by their position. The CountI variable keeps track of what position I want to print.

The only other thing I did was put in a message box to stop and show me the intermediate filter selections so I could QA it and I commented out your printout command. You can delete the msgbox and uncomment your command when you want to go live with the function.

If you have any questions about the logic or anything else I did, please ask.

You might also use a label filter rather than looping through all the items so many times

Please Login or Register  to view this content.