VBA to Take Screenshot of Range

Question

Do you mind helping me with a code that will take a screenshot of a range, say, range A1:C10? All the examples I found online were other people doing a lot more than just a simple screenshot (e.g., pasting it into a different range, creating other worksheets to paste there, opening Word and pasting it there, etc.)—way more than I need. I'm no VBA expert and am having difficulty weeding out the code parts I don't need from those examples. I just need it to do the screenshot; I would then (manually) paste it into a specific section of a Word template I use. I can assign the VBA macro to a button in Excel.

Thank you in advance for any help.

Answer 1

Basically, the manual method is as follows:-

1. Select the required Excel range.
2. Right click over selection and select Copy.
3. Change to Word doc.
4. Right click where the picture is required.
5. Select the picture icon from the options displayed (See screen shot with the icon circled in red)

If using VBA to copy, then the following code example and then start at step 3 of above.

Sub Macro1()

ActiveSheet.Range("A1:C10").Copy 

End Sub

Answer 2

Thank you for this. That can certainly work with, say, Word and Outlook. Sometimes I paste it in Teams, though. Is there a code for it to take a screenshot or copy the range so that it's copied to the clipboard like a screenshot?

Answer 3

AFAIK it is copied to the clipboard otherwise you would not be able to right click and paste. However, I have not tried it in Teams. Your original question referred to "I would then (manually) paste it into a specific section of a Word template" and the method I provided should do that.

Answer 4

I've tried this a few times but it's really not a solution that will work, I'm afraid. Pasting the 'picture' to, say, Word or Outlook seems to only paste a table that isn't editable. With normal screenshots, I add a glow formatting to make it stand out from the background and indicate it's a screenshot. But doing that with the suggested method highlights everything in the 'picture' rather than just the edges like normal. I still need to find a way to take an actual screenshot via VBA macro.

Answer 5

Hi,

Select a range and export in a folder on desktop

folder's name: RngAsPic

if you select the range A1:D5

file's name is: rng-A1D5

Add the below vba

in a regular module .....

'=================

Sub RangeAsPicture_Desktop()

' ## 27-01-2024 ##

Dim ws As Worksheet

Dim rng As Range

Dim sMain As String, sFd As String, sPath As String, rpl As String

sMain = CreateObject("WScript.Shell").SpecialFolders("Desktop") & ""

sFd = "RngAsPic" & ""

sPath = sMain & sFd

If Dir(sMain & sFd, vbDirectory) = Empty Then MkDir sPath

Set rng = Selection

If MsgBox("select a range ?", vbOKCancel) = vbCancel Then Exit Sub

Application.ScreenUpdating = False

Set ws = Worksheets.Add

Charts.Add

ActiveChart.Location Where:=xlLocationAsObject, Name:=ws.Name

With ActiveChart

With .Parent

.Height = rng.Height

.Width = rng.Width

End With

rng.CopyPicture xlScreen, xlPicture

.Paste

rpl = Replace(rng.Address, "$", "")

rpl = Replace(rpl, ":", "")

.Export Filename:=sPath & "rng-" & rpl & ".png", FilterName:="png"

End With

Application.DisplayAlerts = False

ws.Delete

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub