Disabling ScreenUpdating cause copy-paste to throw 1004 error.

ImMaverick

New Member
Joined
Apr 5, 2016
Messages
1
Little background to my question.
(Office 2010, if it helps)
I'm working on the project which should do pretty simple thing - take data from two sources and make printable pages (mean align data on the page properly, so it looks nice when printed).
The problem is - both sets of data should be properly formatted (main idea -I can't freely change column width and row height. Well, it's possible, but it's more pain in it, then possible profit), so I decided to use .Picture.Paste Link:=True to insert one set of data to avoid overformatted pages.
And my code works as intended, but really slowly. Like 15-30 seconds per page and I can have like 100 of them, so I decided to add Application.ScreenUpdating = False and now it can handle 10 or even 20 pages almost instantly. But when there are more then 20 pages error start to occur randomly.
Run-time error '1004':
Method Paste of Pictures class failed
(Or something like that - I'm translating from localized Excel) So
1) It works with screen updating (But so slow. And user shouldn't see that IMHO - that's like being naked in public)
2) If it throws error, I can manually .Copy and .Select and then script will continue for some more iterations (Like 2, or 5, or 50 - totally random)
Code:
        'On Error GoTo RetryHeader <-Doesn't helped
'RetryHeader:
        wsTable.Activate
        wsTable.Range("A5:I6").Select 'Doesn't helped either
        wsTable.Range("A5:I6").Copy '<-can copy this manually (I've mentioned it above) to "bypass" error once
        wsPrint.Activate
        wsPrint.Range("G" & (i * 20) - 18).Select '<-can select this manually(I've mentioned it above) to "bypass" error once
        wsPrint.Pictures.Paste Link:=True '<-Usually this line cause error
        wsPrint.Pictures(wsPrint.Pictures.Count).Name = "picH" & i
        Application.CutCopyMode = False
        With wsPrint.Pictures("picH" & i)
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.PictureFormat.Crop.PictureWidth = 1165
            .ShapeRange.PictureFormat.Crop.PictureOffsetX = 40
        End With
        'On Error GoTo RetryBody
'RetryBody:
        wsTable.Activate
        wsTable.Range("I" & pageBr(i) - 1).Select
        wsTable.Range("A" & pageBr(i - 1) & ":I" & pageBr(i) - 1).Copy
        wsPrint.Activate
        wsPrint.Range("G" & (i * 20) - 17).Select
        wsPrint.Pictures.Paste Link:=True '<- This line can throw same error, but rarely
        wsPrint.Pictures(wsPrint.Pictures.Count).Name = "picB" & i
        Application.CutCopyMode = False
        With Sheets("Print").Pictures("picB" & i)
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.PictureFormat.Crop.PictureWidth = 1165
            .ShapeRange.PictureFormat.Crop.PictureOffsetX = 40
        End With
It looks like excel unload some data when its not "in focus" from memory, so it need to be focused on it again to be able to copy it, but I can't figure how to make it with disabled screen updating.
I'm totally stuck with it, so any advise will be much appreciated.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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