excel - VBA - Range to jpg picture

Question

I'm trying to get a jpg file from a specific range in excel

I'm currently getting the

1004 Runtime error on Range method from _Worksheet object.

This is what my code looks like:

Sub Export()

Dim ws As Worksheet
Dim Rng As Range
Dim Chrt As Chart

Set ws = ActiveSheet
Set Rng = Range("B2:H11")

ws.Range(Rng).CopyPicture
Set Chrt = Charts.Add

With Chrt
    .Paste
    .Export FileName = "Case.jpg", Filtername:="JPG"
End With

End Sub

Answer 1

The main error has @J_Lard mentioned already in his comment.

But I would use ChartObject rather than a Chart sheet. Whith this you can determine the size of the output instead of getting the whole chart area in the picture.

And while using F8 step the paste and export will work, while real time run, the ChartObject needs to be activated.

Sub Export()

 Dim oWs As Worksheet
 Dim oRng As Range
 Dim oChrtO As ChartObject
 Dim lWidth As Long, lHeight As Long

 Set oWs = ActiveSheet
 Set oRng = oWs.Range("B2:H11")

 oRng.CopyPicture xlScreen, xlPicture
 lWidth = oRng.Width
 lHeight = oRng.Height

 Set oChrtO = oWs.ChartObjects.Add(Left:=0, Top:=0, Width:=lWidth, Height:=lHeight)

 oChrtO.Activate
 With oChrtO.Chart
  .Paste
  .Export Filename:="Case.jpg", Filtername:="JPG"
 End With

 oChrtO.Delete

End Sub

If path is not specified, the Case.jpg will be saved in default save location. This is probably your user documents directory C:UsersYourNameDocuments