Pivot Table To PDF Loop?

Question

So I have figured out how to get a pivot table to save to a specified folder as .PDF, but I am curious if anyone knows if there is a way to loop my code instead of having it all separated?

My code works, but am just curious if there is a way to condense it?

Sub Test1234()          

 '''Sales Team1''' 
Sheets("Worksheet1").Activate
'''adjust the range if the Pivot Table moves'''
Range("C3").Select   
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vertical").ClearAllFilters
'''adjust the vertical name in the quotes below'''
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vertical").CurrentPage = _
"Sales Team1"
Sheets("Worksheet1").Select 
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Desktop" & Format(Date, " dd.mm.yyyy")

'''Sales Team2''' 
Sheets("Worksheet1").Activate
'''adjust the range if the Pivot Table moves'''
Range("C3").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vertical").ClearAllFilters
'''adjust the vertical name in the quotes below'''
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vertical").CurrentPage = _
"Sales Team2"
Sheets("Worksheet1").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Desktop" & Format(Date, " dd.mm.yyyy")

 '''Sales Team3''' 
Sheets("Worksheet1").Activate
'''adjust the range if the Pivot Table moves'''
Range("C3").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vertical").ClearAllFilters
'''adjust the vertical name in the quotes below'''
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vertical").CurrentPage = _
"Sales Team3"
Sheets("Worksheet1").Select 
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Desktop" & Format(Date, " dd.mm.yyyy")

End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:34

Try the code below, there's no need to use Activate and Select all the time (it also slows down the code run-time).

Read HERE why you should stay away from Select / Activate / Selection / ActiveSheet etc.

Code

Option Explicit

Sub Test1234()

Dim PvtTbl      As PivotTable
Dim PvtFld      As PivotField
Dim ws          As Worksheet
Dim i           As Long

' set the worksheet where "PivotTable2"
Set ws = Worksheets("Worksheet1")

' set the Pivot Table
Set PvtTbl = ws.PivotTables("PivotTable2")

' set the Pivot Field "Vertical"
Set PvtFld = PvtTbl.PivotFields("Vertical")
With PvtFld
    For i = 1 To 3
        .ClearAllFilters

        'adjust the vertical name in the quotes below
        .CurrentPage = "Sales Team" & i
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Desktop" & Format(Date, " dd.mm.yyyy")
    Next i
End With

End Sub

Pivot Table To PDF Loop?

1 Answers1