[Excel VBA] How to Copy a Cell Range and Paste It as a Picture

Sometimes, you may want to paste a part of a table or report as a “snapshot” to another location. This ensures that the values and formatting remain fixed and do not change.

By using the .CopyPicture method in VBA, you can copy a cell range exactly as it appears on the screen and paste it as an image. This allows you to easily create fixed report components that are not affected even if the original data changes.

This article explains the basic method for copying and pasting a cell range as a picture.

Complete VBA Code

Below is the VBA code that copies the range B2:E10 from the sheet named “DataSheet” and pastes it as a picture into cell C3 of the “ReportSheet”.

Sub CopyRangeAsPicture()

    ' Declare variables
    Dim sourceRange As Range
    Dim destinationCell As Range
    
    '--- Settings ---
    ' Source cell range
    Set sourceRange = ThisWorkbook.Worksheets("DataSheet").Range("B2:E10")
    ' Destination cell
    Set destinationCell = ThisWorkbook.Worksheets("ReportSheet").Range("C3")
    '--- End Settings ---
    
    Application.ScreenUpdating = False
    
    ' --- 1. Copy the specified range as a "picture" ---
    ' Copy using the screen appearance and picture format
    sourceRange.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    
    ' --- 2. Activate the destination sheet and cell ---
    destinationCell.Parent.Activate
    destinationCell.Select
    
    ' --- 3. Paste as a "picture" on the active sheet ---
    ActiveSheet.Pictures.Paste
    
    ' (Optional) Set the name of the pasted picture
    ' With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    '     .Name = "Snapshot_Table"
    ' End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Pasted the cell range as a picture."

End Sub

Key Points of the Code

1. Copy Cell Range as a Picture: .CopyPicture

sourceRange.CopyPicture Appearance:=xlScreen, Format:=xlPicture

This command copies the cell range to the clipboard as a “picture.” Unlike the standard .Copy method, this copies the visual appearance of the range as image data, not the actual cell data.

The .CopyPicture method has two important arguments:

• Appearance:
  • xlScreen (Default): Copies exactly as it appears on the screen. This is commonly used.
  • xlPrinter: Copies as it would look when printed.
• Format:
  • xlPicture (Default): Copies in picture format. This is a vector image that maintains quality even when resized.
  • xlBitmap: Copies in bitmap format. This is an older format; xlPicture is usually higher quality.

2. Paste as a Picture: .Pictures.Paste

ActiveSheet.Pictures.Paste

This pastes the information copied to the clipboard as a picture onto the worksheet. When you run this method, the image is inserted with the top-left corner anchored to the currently active cell (the cell selected with .Select).

The pasted image is treated as a Shape object in VBA, so you can adjust its size or position later if needed.

Summary

The process to fix a cell range as a “picture” and paste it onto a sheet involves these two steps:

1. Run the .CopyPicture method on the source Range object.
2. Select the destination cell and run the ActiveSheet.Pictures.Paste method.

This technique is very effective in the following situations:

• When you want to place a snapshot of a summary table or chart at a specific point in time on a dashboard.
• When you want to create report parts where you want to prevent value changes caused by user editing or formula recalculations.
• When you want to quickly generate a good-looking image of a table to paste into an email body.

By using .Copy and .CopyPicture correctly, you can greatly expand the possibilities of report creation in VBA.