Excel 2010 VBA: Runtime error 1004, Microsoft Excel cannot paste the data

Question

I had been running a cut and paste picture routine for some time and all of a sudden Excel starting giving me this run time error. It had been working fine for several days until now (no OS update or reboot, though I did try closing and reopening Excel to see if it helped). Stranger still, the script does a batch copy and paste.picture, the same range (with recalculated values) is copied and pasted 13 times and the error message pops up usually in the last loop or occasionally at some random point.

I looked up support.microsoft.com/en-us/kb/905164: "This issue may occur if either of the following conditions is true:

The Microsoft Visual Basic for Applications (VBA) macro copies and pastes one whole row in an Excel 2003 workbook.
The Microsoft VBA macro copies and pastes a range of 2,516 rows or more rows in an Excel 2003 workbook."

However, I am copying a range of 12,12 cells, from A1 to L12 to be exact, not even close to an entire row. I have tried using range.offset, xldown, rannge(cells(1,1), cells(12,12)) but none of these helped.

Has anyone experienced something similar?

Sub PutPic(ByRef FN As String)


 Dim fname As String
 fname = "E:\Users\ABCD\Documents\EFGH\" & FN
 Worksheets(2).Range(Cells(1, 1), Cells(12, 12)).Select
 'Sheets("sheet2").Range("A1:l12").Select
 Selection.Copy
 'Sheets("sheet2").Range("a1").Select
 ActiveSheet.Pictures.Paste(Link:=False).Select
 Selection.Name = "Pic"
 Selection.ShapeRange.ScaleWidth 2, msoFalse, msoScaleFromTopLeft
 Selection.ShapeRange.ScaleHeight 2, msoFalse, msoScaleFromMiddle

 Dim ChtObj As ChartObject
 With ThisWorkbook.Worksheets(2)
    .Activate
    Set ChtObj = .ChartObjects.Add(100, 100, 400, 400)
    ChtObj.Name = "PicFrame"
    ChtObj.Width = .Shapes("Pic").Width
    ChtObj.Height = .Shapes("Pic").Height

    ActiveSheet.Shapes.Range(Array("Pic")).Select
    Selection.Copy

    ActiveSheet.ChartObjects("PicFrame").Activate
    ActiveChart.Paste

    ActiveChart.Export Filename:=fname, FilterName:="png"
    ChtObj.Delete
    ActiveSheet.Shapes.Range(Array("Pic")).Delete

 End With
End Sub

The sub with the looping routine, totally ordinary which feeds a filename to the subroutine.

Public Sub MainRun()
 Dim i, j, k As Long
 Dim NMG, NMB As String

 Dim FNGBSig As String
 Dim FNUnivSig As String
 Dim BatchStart, Batch As Long
 BatchStart = ThisWorkbook.Worksheets(2).Cells(15, 1).Value + 1
 Batch = 13
 For i = BatchStart To BatchStart + Batch - 1

'Some calculations that refresh values in range A1:L12

FNGBSig = i & "GoodBad.png"

        PutPic FNGBSig

 Next i

End Sub

Just use the top-left cell for the target. The remainder of the cells will paste in adjoining cells. — user4039065
– user4039065, Commented Sep 28, 2016 at 16:14
Also, after the error message (and going into debug), I could manually paste the picture. — user110084
– user110084, Commented Sep 28, 2016 at 16:15
Please revise your question to include the code which is raising the error. This sounds like it might be a buffer issue that could be resolved with a small delay. — David Zemens
– David Zemens, Commented Sep 28, 2016 at 16:16

David Zemens · Accepted Answer · 2016-09-28 18:46:02Z

1

I suspect the loop is causing the issue as the .Export method is running in to itself. Use the WinAPI Sleep function to insert a small delay (1 second is probably enough). Also, I've cleaned up the code a little bit:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For use in timer function

Sub PutPic(FN)

Dim fname As String
Dim shp As Picture
Dim ChtObj As ChartObject
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(2)

fname = "E:\Users\ABCD\Documents\EFGH\" & FN

'Copy the range of cells
With ws
    .Range(.Cells(1, 1), .Cells(12, 12)).Copy

    'Paste & get a handle on the resulting picture:
    Set shp = .Pictures.Paste(Link:=False)
End With

'Scale your picture:
With shp
    .ShapeRange.ScaleWidth 2, msoFalse, msoScaleFromTopLeft
    .ShapeRange.ScaleHeight 2, msoFalse, msoScaleFromMiddle
End With

'Add the ChtObj frame:
Set ChtObj = ws.ChartObjects.Add(100, 100, 400, 400)
'Size the chart, paste the picture in the chart, export
With ChtObj
    .Width = shp.Width
    .Height = shp.Height
    shp.Copy
    Sleep 1000  '1000 milliseconds = 1 second
    .Chart.Paste
    .Chart.Export Filename:=fname, FilterName:="png"
    .Delete
End With

shp.Delete

End Sub

Note that this is generally frowned upon:

 Dim i, j, k As Long
 Dim NMG, NMB As String

 Dim FNGBSig As String
 Dim FNUnivSig As String
 Dim BatchStart, Batch As Long

This declares i as Variant, j as Variant, k as Long, etc. To do multiple declarations inline, you still need to specify the data type:

Dim i as Long, j as Long, k as Long
Dim NMG as String, NMB as String
' etc...

edited Sep 28, 2016 at 18:46

answered Sep 28, 2016 at 16:54

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user110084 Over a year ago

Thanks @davidzemens. Appreciate the guidance on bad practice too.

David Zemens Over a year ago

No worries, it is a common oversight :)

user110084 Over a year ago

Sorry I have not yet been able to run your code, but will do that in a couple of hours. Your guidance on DIM actually helped resolved one other problem - I kept getting error with undeclared variables when calling subs and functions despite having done so. I did have to separate each of the variables into its own DIM. Now I see why what I was doing is frown upon!

user110084 Over a year ago

your code work well with one change - Sleep was needed before the paste and does not appear to need it after export. I was able to get it down to 100ms without complaint. Thank you so much.

user110084 Over a year ago

I finally got to the bottom of this behaviour. There is a dynamic name range in the native spreadsheet and it is updated in the background after each loop. As the range grew longer, it was taking longer to recalc the sheet. Removing the name range and moving the calculations on the sheet to VBA eliminated the problem. I kept SLEEP 200 in the code just in case.

|

Collectives™ on Stack Overflow

Excel 2010 VBA: Runtime error 1004, Microsoft Excel cannot paste the data

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related