Macro to Create a Picture of the Selection

This VBA macro saves the selected cells, such as a summary table or a dashboard, as a PNG image in the same folder as the workbook, ready to share on WhatsApp or paste into a presentation.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Save the selection as PNG

Select the cells and run:

Sub SaveSelectionAsPicture()
    Dim r As Range, co As ChartObject, f As String
    If TypeName(Selection) <> "Range" Then Exit Sub
    If ActiveWorkbook.Path = "" Then MsgBox "Save the workbook first.": Exit Sub
    Set r = Selection
    r.CopyPicture xlScreen, xlPicture
    Set co = ActiveSheet.ChartObjects.Add(0, 0, r.Width, r.Height)
    co.Activate
    co.Chart.Paste
    f = ActiveWorkbook.Path & Application.PathSeparator & "Selection " & Format(Now, "yyyy-mm-dd hhmmss") & ".png"
    co.Chart.Export f
    co.Delete
    MsgBox "Saved: " & f
End Sub

The macro copies the cells as a picture, pastes it into a temporary chart of the same size, exports the chart as a PNG, then deletes the chart. The time in the file name means each run creates a new file.

Without a macro

  1. Select the cells and go to Home → Copy → Copy as Picture (the arrow next to Copy).
  2. Paste into WhatsApp, PowerPoint or Paint, and save from there.

In Microsoft 365 you can also select the cells, press Ctrl + C, and paste straight into most apps as an image.

Things to know

  • The image looks the way the cells look on screen, so hide gridlines first (View → Gridlines) for a cleaner picture.
  • If the image comes out blank, run the macro again. Excel occasionally needs a moment to paste into the chart.
  • On a Mac, Excel may ask for permission to save to the folder.

Related macros