Macro fr Pivot field controlled by a cell in another sheet

[vikash200418]

I am in need of a macro which where the a particular field of pivot is governed by a input from a separate sheet and then the macro prints the data into PDF. I am attaching herewith a sample file which will give you a idea of what I am looking at. The cells to be inputed are highlighted in yellow and based upon those inputs the pivot reacts and print that particular details in PDF.

[Admin]

Hi Vikash,

try something like..


Option Explicit
Sub kTest()

Dim TeamName As String
Dim EmpName As String
Dim FName As String
Dim ShtInput As Worksheet
Dim Pvt As PivotTable

Set Pvt = Worksheets("Pivot 1").PivotTables("PivotTable3")
Set ShtInput = Worksheets("Input Sheet")

TeamName = ShtInput.Range("c5").Value
EmpName = ShtInput.Range("c6").Value
FName = ShtInput.Range("c7").Value & ".pdf"

With Pvt
.PivotFields("Team").ClearAllFilters
.PivotFields("Team").CurrentPage = TeamName
CreatePDF .TableRange1, FName 'replace .TableRange1 with .TableRange2 if you need team name as well
End With

End Sub
Sub CreatePDF(ByRef Range2Print As Range, Optional PDFFileName As String)

If Len(PDFFileName) = 0 Then
PDFFileName = ThisWorkbook.Path & "\Test.pdf"
End If

Range2Print.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub