Results 1 to 2 of 2

Thread: Macro fr Pivot field controlled by a cell in another sheet

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    5
    Rep Power
    0

    Macro fr Pivot field controlled by a cell in another sheet

    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.
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Vikash,

    try something like..

    Code:
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  3. Replies: 5
    Last Post: 03-09-2013, 09:01 AM
  4. Replies: 3
    Last Post: 03-05-2013, 03:57 PM
  5. Replies: 1
    Last Post: 02-10-2013, 06:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •