VBA Error 1004 - CopyPicture method of Range class failed

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Hi all.

I am trying to run the following code one after another for three different ranges.
The macro extracts the ranges and save them as pictures.



Code:
 Sub ExportBRRangeToPNG()  '--exports selected range to png file
  '  jpg is not appropriate format for this output
  '  default filename is address of selected range
  '  based on code example posted at:
  '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html
  '  modified at
  '     http://www.mrexcel.com/forum/excel-questions/751500-export-range-cells-jpg.html#post3691601
  '  further refined by Jon Peltier, Peltier Technical Services, Inc.


  Dim vBRFilePath As String
  Dim rBRSelection As Range
  Dim sBRDefaultName As String
  Dim t As Variant


  If TypeName(Selection) <> "Range" Then
    MsgBox "Selection is not a range of cells."
    Exit Sub
  End If


  Set rBRSelection = Worksheets("DirMail").Range("A60:K65")
  
'  t = Now + TimeValue("00:00:01")
  
  


'  sBRDefaultName = Replace(rBRSelection.Address(0, 0, xlA1, 1), ":", "to") & ".png"
    sBRDefaultName = "Oralcare.png"
  vBRFilePath = "G:\ASTER\11 Production\DDS\Aster DDS output files\BR.png"
  '--exit if cancelled by user
'  If vBRFilePath = False Then Exit Sub


  '-- copy selected range as picture (not as bitmap)
  Application.CutCopyMode = False
[B][COLOR=#b22222]  rBRSelection.CopyPicture Appearance:=xlScreen, Format:=xlPicture[/COLOR][/B]


  '--Create an empty chart, slightly larger than exact size of range copied
  With ActiveSheet.ChartObjects.Add( _
      Left:=rBRSelection.Left, Top:=rBRSelection.Top, _
      Width:=rBRSelection.Width + 0.1, Height:=rBRSelection.Height + 0.1)


    With .Chart
      ' clean up chart
      .ChartArea.Format.Line.Visible = msoFalse
      .ChartArea.Select
      ' paste and position picture
      .Paste
      
      
'      Do While t > Now
'        DoEvents
'      Loop
        
      
'      With .Pictures(1)
'        .Left = .Left
'        .Top = .Top
'      End With
      
      ' export
      .Export CStr(vBRFilePath)
    End With
    
    ' remove no-longer-needed chart
    .Delete
  End With
End Sub

I am calling the same macro three times, each time extracting a different range.

Code:
Sub ExporttoJpg()




Call ExportOCRangeToPNG
Call ExportBRRangeToPNG
Call ExportSummaryRangeToPNG


End Sub
On the line that is red I receive random 1004 - CopyPicture method of Range class failed errors.

I can run the three macros a number of time then the error pops up. - But not at the same subroutine ?!?

I clear the error and it runs again.
The line it craps out, is always the .copypicture one but in different subs.

Any idea of what am I doing wrong?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Code:
I am calling the same macro three times, each time extracting a different range.
The statment does not correspond with this part of your post:
Code:
Sub ExporttoJpg()
Call ExportOCRangeToPNG
Call ExportBRRangeToPNG
Call ExportSummaryRangeToPNG
End Sub
It looks like you are calling three separate macros from a control macro. It is possible that residual memory is building up and causing problems. You could try setting your Object variables to Nothing before the end of each of the three called macros, which would dump any stored memory for that variable. Those would normally be dumped when the macro ends, but since they are called within a control macro, they might no be dumped, so setting them to Nothing will cover that scenario. Errors in multiple copy and paste in Excel is not uncommon. I have seen several posts about that issue, but I do not recall ever seeing one that really tied down the cause of it.
 
Upvote 0
Hi JLGWhiz.

Thanks for the reply.
I meant that I am calling the "same" macro three times, that I am calling three different macro (didn't wanted to copy the long code three times).
I think you are right about the memory, as when I tested the macro (and ran the control macro) sometimes it ran O.K., but when I repeated the control macro a bit quicker, then it crapped out.

How would I set the objects to nothing?

-Would it go in between the three calls in the control macro or the end of each subroutine?
-Which variables would I dump?, would an Application.CutCopyMode = False be enough?

Thanks

Tom
 
Upvote 0
In the macro you posted, just before the 'End Sub' line put:
Code:
Set rBRSelection = Nothing
That frees the reserved memory for that Object variable. You only need to do it for Object variables, not for the variables that represent text or values. Do that for the object variable you set in each of your called macros.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top