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?

 
Sub PrintSelection()

Dim Cval1 As Variant
Dim Cval2 As Variant
Dim Rng As Range

Dim Table As PivotTable
Dim All As Range
Dim PvI As PivotItem

    Set All = Worksheets("PIVOT").Range("A1:AZ10000")
    Set Table = Worksheets("PIVOT").PivotTables("PivotSchedule")
    For Each PvI In Table.PivotFields("Dayname").PivotItems

        Cval1 = ActiveSheet.Range("J2").Value
        Cval2 = ActiveSheet.Range("K2").Value
        Set Rng = Range(Cval1 & ":" & Cval2, Selection.End(xlDown))
        Rng.Select
        Application.PrintCommunication = True
        Selection.PrintOut Copies:=1, Collate:=True

    Next
End Sub

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

Sub PrintSelection()

Dim Table As PivotTable     ' Pivot Table
Dim PvF As PivotField       ' Pivot Field
Dim dayList As Variant, theDay As Variant

dayList = Split("Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday", "|")
' Initalize variables
Set Table = Worksheets("PIVOT").PivotTables("PivotSchedule")
Set PvF = Table.PivotFields("Dayname")
  
For Each theDay In dayList
    PvF.ClearAllFilters
    PvF.PivotFilters.Add Type:=xlCaptionEquals, Value1:=theDay
    Table.TableRange1.Select
    MsgBox "Stop and look"
    'Selection.PrintOut Copies:=1, Collate:=True
Next theDay
    
End Sub