@GMorrod
Make sure that you display the Developer tab of the ribbon.
(If you don't see it, select File > Options, then click Customize Ribbon, tick the check box Developer in the list of Main Tabs, and click OK.)
Activate the Developer tab and click Visual Basic in the Code group.
Select Insert > Module to create a new code module.
Copy the following macro into the module:
Sub ShowRange()
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim i As Long
Set wsh1 = ActiveSheet
' This will delete the first picture found on the active sheet
For i = wsh1.Shapes.Count To 1 Step -1
If wsh1.Shapes(i).Name Like "Picture*" Then
wsh1.Shapes(i).Delete
Exit Sub
End If
Next i
' Change Sheet2 to the actual name of the sheet with the range to capture
Set wsh2 = Worksheets("Sheet2")
' Change D8:F9 to the range you want to capture
wsh2.Range("D8:F9").CopyPicture Appearance:=xlScreen, Format:=xlPicture
' Change K2 to the cell where the top left corner of the picture should be
wsh1.Range("K2").Select
wsh1.PasteSpecial Format:="Picture (Enhanced Metafile)"
End Sub
See the remarks on where you have to edit the code.
Close the Visual Basic Editor.
Make sure that the sheet where you want the button is the active sheet.
In the Controls group of the Developer tab of the ribbon, click Insert > Command Button (Form Control).
Click on the worksheet where you want the button.
Excel will ask you to assign a macro.
Select ShowRange, then click OK.
Click outside the button.
Then click the button to test it.
Finally, save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.
