Saving Pivot table to PDF

[saloelrn1988]

Hi I need Help!

i am able to save my Pivot table in PDF however upon saving it saves other pages with blanks. i wanted to save it up until the cell containing data(my last cell may vary because I'm using different data per week).

See below coding that i used:

Sub Save_PDF()
'
' Save_PDF Macro
' Save pivot table as PDF
Dim saveLocation As String
saveLocation = "C:\Users\elon\Desktop\" & Sheets("email data").Range("B3")

Sheets("pivot table").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Worksheets("raw data").Select
End Sub

 

[Domenic]

You can use the ExportAsFixedFormat method of the pivot table range, instead of the active sheet...

Option Explicit

Sub Save_PDF()

    Dim saveLocation As String
    saveLocation = "C:\Users\elon\Desktop\" & Sheets("email data").Range("B3").Value
    
    Dim targetPivotTable As pivotTable
    Set targetPivotTable = Sheets("pivot table").PivotTables("PivotTable1") 'change the pivot table name accordingly
    
    Dim targetExportRange As Range
    Set targetExportRange = targetPivotTable.TableRange2 'range includes the page field
    
    targetExportRange.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveLocation, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
End Sub

Hope this helps!

 

[saloelrn1988]

You can use the ExportAsFixedFormat method of the pivot table range, instead of the active sheet...

Option Explicit

Sub Save_PDF()

    Dim saveLocation As String
    saveLocation = "C:\Users\elon\Desktop\" & Sheets("email data").Range("B3").Value
   
    Dim targetPivotTable As pivotTable
    Set targetPivotTable = Sheets("pivot table").PivotTables("PivotTable1") 'change the pivot table name accordingly
   
    Dim targetExportRange As Range
    Set targetExportRange = targetPivotTable.TableRange2 'range includes the page field
   
    targetExportRange.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveLocation, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
       
End Sub

Hope this helps!

hi, I'm not that knowledgeable when it come to coding.

how would i select a pivot name?

and what do you mean in "range includes the page field" should i make any modification on that as well?

 

[Domenic]

how would i select a pivot name?

Select a cell within your pivot table, and check the ribbon for the name of the pivot table...

Ribbon >> PivotTable Analyze tab >> PivotTable group >> PivotTable Name

Alternatively, you can refer to your pivot table by index. For example, since the worksheet contains only one pivot table (I've assumed so from your original post) , you can refer to it using index number 1...

Set targetPivotTable = Sheets("pivot table").PivotTables(1)

and what do you mean in "range includes the page field" should i make any modification on that as well?

**Edited**

It means that the cells containing the page field or page fields will be included in the exported PDF. If for some reason you don't want to include them, you would use the TableRange1 object instead of TableRange2...

Set targetExportRange = targetPivotTable.TableRange1

 

[saloelrn1988]

Select a cell within your pivot table, and check the ribbon for the name of the pivot table...

Ribbon >> PivotTable Analyze tab >> PivotTable group >> PivotTable Name

Alternatively, you can refer to your pivot table by index. For example, since the worksheet contains only one pivot table (I've assumed so from your original post) , you can refer to it using index number 1...

Set targetPivotTable = Sheets("pivot table").PivotTables(1)

It means that the cells containing the page field or page fields will be included in the exported PDF. If for some reason you don't want to include them, you would use the TableRange object instead of TableRange2...

Set targetExportRange = targetPivotTable.TableRange

i already found the table name however im having a run time error once i execute my macro a

 

[Domenic]

Sorry, that should have been TableRange1...

Set targetExportRange = targetPivotTable.TableRange1

 

[saloelrn1988]

I'm still having a runtime error, please see below:

 

[Domenic]

You didn't say which error you're getting, but it's probably a "Run-time error 1004: Document not saved ... ".

Try running the macro again. This time, though, when the error occurs and you click on Debug, move your cursor over the variable saveLocation.

Has it been assigned a valid path and filename?

And, if so, do you have permissions to access the folder?

 

[saloelrn1988]

below is my runtime error, also the path that i used in the beginning is the same as the path that I'm using now.

Actually the path is on my desktop

 

[Domenic]

For testing purposes only, try replacing...

Filename:=saveLocation, _

with

Filename:="C:\Users\" & Environ("username") & "\Desktop\sample.pdf", _

Do you still get an error?