[Excel VBA] How to Paste a Cell Range as a “Linked Picture” That Automatically Updates

When creating dashboards or reports in Excel, you often have a sophisticated need: “I want to paste a table or chart from another sheet so it looks good, but I also want the pasted content to automatically update if the source data changes.”

The powerful Excel feature “Linked Picture” realizes this. Using VBA, you can automate the process of pasting these linked pictures.

In this article, I will explain the VBA code to copy a specific cell range and paste it as an image that maintains a link to the original data.

Completed VBA Code

Below is the VBA code that copies the range B2:E10 from the “SourceData” sheet and pastes it as a “Linked Picture” into cell C3 of the “Dashboard” sheet.

Sub PasteRangeAsLinkedPicture()

    ' Declare variables
    Dim sourceRange As Range
    Dim pasteCell As Range
    
    '--- Settings ---
    ' Source cell range to copy
    Set sourceRange = ThisWorkbook.Worksheets("SourceData").Range("B2:E10")
    ' Destination cell
    Set pasteCell = ThisWorkbook.Worksheets("Dashboard").Range("C3")
    '--- Settings End ---
    
    Application.ScreenUpdating = False
    
    ' --- 1. Copy the source range as usual ---
    sourceRange.Copy
    
    ' --- 2. Activate the destination sheet and cell ---
    ' Note: To select a cell, the sheet must be active first
    pasteCell.Parent.Activate
    pasteCell.Select
    
    ' --- 3. Paste as "Linked Picture" ---
    ' Set the Link argument of the Pictures.Paste method to True
    ActiveSheet.Pictures.Paste Link:=True
    
    ' (Optional) Set the name of the pasted shape
    ' With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    '     .Name = "LinkedReportTable"
    ' End With

    Application.ScreenUpdating = True
    
    MsgBox "The cell range has been pasted as a Linked Picture."

End Sub

Key Points of the Code

1. Normal Copy (.Copy)

sourceRange.Copy

The process begins with a standard copy of the cell range. Executing the .Copy method on the target Range object sends the range information to the clipboard.

2. Paste as Linked Picture (.Pictures.Paste(Link:=True))

ActiveSheet.Pictures.Paste Link:=True

This is the core of this technique. ActiveSheet.Pictures.Paste is a method that pastes clipboard content as a picture (image).

By setting the optional argument Link to True, it is pasted not as a simple static image, but as a special image that holds “link” information to the original cell range.

Behavior of “Linked Picture”

After running this macro and pasting the picture, try going back to the source “SourceData” sheet and changing the values or formatting (such as background color) in the B2:E10 range.

When you check the “Dashboard” sheet afterward, you will see that the appearance of the pasted picture has automatically updated to reflect the changes in the source data.

This feature clarifies the division of roles: data management is done on the data sheet, and report layout is done on the dashboard sheet. This allows you to create highly maintainable files.

Summary

The procedure for creating a “Linked Picture” with VBA that automatically reflects the original data is as follows:

1. Execute the standard .Copy method on the source Range object.
2. Select the destination cell.
3. Execute ActiveSheet.Pictures.Paste Link:=True.

With just this single argument Link:=True, you can place a dynamic “live object” on your sheet instead of a static image. This is an extremely useful technique for creating dashboards and reports that look professional and always stay up-to-date.