This will allow you to pick a location. I think it's about as good as you'll get with this method and jpg files- if you change the type to BMP, you'd get better quality but a much larger file
Sub ExportLeagueTable()
Dim oWs As Worksheet
Dim oRng As Range
Dim oChrtO As ChartObject
Dim lWidth As Long, lHeight As Long
Dim saveFileName As String
Set oWs = ActiveSheet
Set oRng = oWs.Range("A1:P26")
Const cPICTURE_TYPE As String = "JPG"
saveFileName = Application.GetSaveAsFilename("NBL League Table 18." & cPICTURE_TYPE, cPICTURE_TYPE & " files (*." & cPICTURE_TYPE & "), *." & cPICTURE_TYPE)
If saveFileName = "False" Then Exit Sub
oRng.CopyPicture xlScreen, IIf(cPICTURE_TYPE = "BMP", xlBitmap, 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:=saveFileName, Filtername:=cPICTURE_TYPE
End With
oChrtO.Delete
End Sub
Bookmarks