0

I am working on data wherein based on filter condition, I want to save my table as image.

Tried working on this code but getting error 'Object Required'

Pls help

Sub Example1()
Dim i As Integer
Dim intCount As Integer
Dim objPic As Shape
Dim objChart As Chart


'copy the range as an image

Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$282").AutoFilter Field:=2, Criteria1:= _
    "ABCD"


Set Str = Range("A1").CurrentRegion.Select **'This I Need to transfer as Image**


Call Sheet1.Range("A1").CurrentRegion.Select.CopyPicture(xlScreen, xlPicture) 'Getting Error


'remove all previous shapes in sheet2
intCount = Sheet2.Shapes.Count
For i = 1 To intCount
Sheet2.Shapes.Item(1).Delete
Next i
'create an empty chart in sheet2
Sheet2.Shapes.AddChart
'activate sheet2
Sheet2.Activate
'select the shape in sheet2
Sheet2.Shapes.Item(1).Select
Set objChart = ActiveChart
'paste the range into the chart
objChart.Paste
'save the chart as a JPEG
objChart.Export ("C:\Output\StuffBusinessTempExample.Jpeg")
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dipal Shah
  • 1
  • 2
  • In which line do you get the error? – Pᴇʜ May 15 '20 at 07:23
  • @Pᴇʜ this line Call Sheet1.Range("A1").CurrentRegion.Select.CopyPicture(xlScreen, xlPicture) – Dipal Shah May 15 '20 at 07:47
  • Remove the `.Select` in that line. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 15 '20 at 07:53
  • Thanks @Pᴇʜ. It worked. Is there any way i can manage the size of object. The image which I am trying to convert are of different size. Some are having only 2 rows and some might have 10 rows.... – Dipal Shah May 15 '20 at 11:22
  • This is because you use `.CurrentRegion` and it is not very reliable. If you define the size of your range properly they are all the same size: `Sheet1.Range("A1:D10").CopyPicture(xlScreen, xlPicture)` will always copy the same range `A1:D10`. – Pᴇʜ May 15 '20 at 11:25
  • @Pᴇʜ I can not define pre-fixed range as number of rows can vary. I am repeating the same script for 10 times with new filter value each time. Please suggest incase any better way to handle this – Dipal Shah May 15 '20 at 11:33

0 Answers0