How to save a PivotTable as an image using VBA?

To save a PivotTable as an image using VBA, you can use a combination of Excel’s chart or shape exporting capabilities and clipboard functionality. Here’s a step-by-step guide to achieve this:

Set up the Range: Identify and select the range of the PivotTable you want to save as an image.
Copy the Range to Clipboard: Copy the defined range containing the PivotTable to the clipboard.
Paste as Picture: Paste the contents from the clipboard into a worksheet as an image to allow for export.
Export the Picture: Save the pasted image as a file on your computer.

Here is a VBA example illustrating these steps:

Sub SavePivotTableAsImage()
    Dim ws As Worksheet
    Dim pvtRange As Range
    Dim imgPath As String
    Dim imgName As String
    Dim pic As Picture

    ' Set the worksheet and the PivotTable range
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your sheet name
    Set pvtRange = ws.Range("B2:E10") ' Change to your PivotTable range
    
    ' Copy the range to the clipboard
    pvtRange.Copy
    
    ' Create a temporary chart and paste the clipboard as a picture
    With ws.ChartObjects.Add(Left:=pvtRange.Left, Top:=pvtRange.Top, Width:=pvtRange.Width, Height:=pvtRange.Height)
        .Chart.Paste
        Set pic = .Chart.Pictures(1)
        .Delete ' Deletes the chart but keeps the picture
    End With
    
    ' Specify the image path and name
    imgPath = "C:PathToYourDirectory" ' Change the path as needed

facebooklinkedin