How to Copy and Paste Range or Chart object as Picture in Excel

This post will introduce two methods to copy and paste a range or chart object as a picture in Excel. The first method involves using Excel’s built-in “Copy as Picture” feature, while the second method uses VBA code to automate the process.

The VBA code method will select a range or chart object using Application.Inputbox function and choose a destination cell for the picture. The VBA code will then copy the range or chart object as a picture and paste it into the specified cell.

Copy and Paste Range or Chart object as Picture using Copy as Picture

You can use the “Copy as Picture” feature in Excel to copy a range or chart object as a picture. Here are the steps:

Step1: Select the range or chart object that you want to copy as a picture.

Step2: Go to the “Home” tab in the Excel Ribbon. Click the drop-down arrow next to the “Copy” button in the “Clipboard” section. Select “Copy as Picture” from the drop-down menu. The Copy Picture dialog box will open.

Step3: In the “Copy Picture” dialog box, select the Format as Picture, and select Appearance as shown on screen.

Step4: Click the “OK” button to copy the range or chart object as a picture.You can paste the picture wherever you want by pressing “Ctrl + V” on the keyboard.

Copy and Paste Range or Chart object as Picture with VBA Code

You can also use the VBA Code to copy a range or chart object and paste it in your Excel worksheet, just do the following steps:

Step1: Open your Excel workbook.

Step2: Press Alt + F11 to open the VBA editor.

Step3: In the VBA editor, select Insert from the menu bar, then choose Module to create a new module.

Step4: Copy the VBA code I provided and paste it into the new module. Save the workbook and close the VBA editor.

Sub CopyAsPicture_excelgeek()
    Dim rng As Range
    Dim chartObj As ChartObject
    Dim destCell As Range
    
    'Prompt user to select range or chart object
    On Error Resume Next
    Set rng = Application.InputBox("Select a range or chart object to copy as picture", Type:=8)
    On Error GoTo 0
    
    'If user selects a range, copy it as picture
    If Not rng Is Nothing Then
        rng.CopyPicture xlScreen, xlPicture
        
        'Prompt user to select destination cell
        On Error Resume Next
        Set destCell = Application.InputBox("Select destination cell to place picture", Type:=8)
        On Error GoTo 0
        
        'If user selects a cell, paste the picture and adjust its size
        If Not destCell Is Nothing Then
            With destCell.Parent.Pictures.Paste
                .Left = destCell.Left
                .Top = destCell.Top
                .Width = rng.Width
                .Height = rng.Height
            End With
        End If
    'If user selects a chart object, copy it as picture
    ElseIf ActiveChart Is Nothing Then
        MsgBox "Please select a range or chart object."
    Else
        Set chartObj = ActiveSheet.ChartObjects(ActiveChart.Parent.Name)
        chartObj.Chart.Export Environ$("temp") & "\temp.png", "PNG"
        With destCell.Parent.Pictures.Insert(Environ$("temp") & "\temp.png")
            .Left = destCell.Left
            .Top = destCell.Top
            .Width = chartObj.Width
            .Height = chartObj.Height
        End With
        Kill Environ$("temp") & "\temp.png"
    End If
End Sub

Step5: Press Alt + F8 to open the Macros dialog box. Select the CopyAsPicture_excelhow macro from the list and click the Run button.

Step6: Select the cell where you want to paste the picture.

Step7: select one the destination cell to place picture.

Step8: The picture will be pasted into the selected cell.