Intermittent error pasting a picture from one workbook into another.

[OaklandJim]

Team Mr Excel

I hope that someone is willing to assist with my issue. This has really got me down.

In a workbook -- that I have spent A LOT of time on -- I get an intermittent error. Most times code works flawlessly, other times it crashes mid operation. The error really is intermittent! Here is what workbook does and where the issue crops up.

The "master" workbook (the one with the code) opens a "data" workbook and formats it. That works correctly.

Data in the "data" workbook includes rows for what are called centers. A center is a purchaser of product.

For each center my code creates a copy of a "template" workbook which becomes the "target" workbook. That works correctly.

Then, in the "data" workbook code hides rows that are not for the respective center being processed. That works too.

Code then clears the clipboard. That works correctly.

Code then makes a picture of the data in the "data" workbook by copying the range of data for the center being processed. No problem there.

The picture is pasted into the "target" workbook, into the cell specified by code. BUT during about 5% of iterations code crashes when doing the paste of the picture into the "target" workbook. Sometimes code actually processes all centers as expected. (There are 25 centers.)

I know that a snippit of code won't allow debugging but the offending code is the paste command shown below.

'   ----------------------------------------------------------
        sStepID = "6.c. making picture of data for a center"
'   ----------------------------------------------------------

'       Update values in grand total line to include sum of visible rows
'       (for the center being processed).
        Application.Calculate

'       Create a picture of the data for the center.
        rAllRawData.Copy

'   ---------------------------------------------------------------------
        sStepID = "6.d. putting picture of data into center workbook"
'   ---------------------------------------------------------------------

'       Activate "target" workbook with one worksheet.
        wbSSCD_CS_IRAA.Activate
        
'       Activate 1st (anchor) cell in the header range as target for paste.
        Range("AnchorCell").Activate
        
'       Paste picture into the workbook. Crash about 5% of the time.
        Set oPicture = ActiveSheet.Pictures.Paste

'       Make the picture opaque.
        With oPicture.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
        End With

Any thoughts about what I might try to stop the intermittent behavior?

 

[jolivanes]

Have a read through this.

[SOLVED] VBA help to move all images from a cell, not folder?

Hi all, I have a sheet with 8 images in a column F2:F9. Each cell in F2:F9 has one image with corresponding details. I'd like to move all these images to the square box in the 8 card templates I've made on Excel. The square box example can be seen from cell D8:E12.I was referring to this thread...

www.vbaexpress.com

Sometimes a single "DoEvents" works and other times not.
A well respected guru came up with the double "DoEvents" but even that errored on occasion. Like you mentioned, very seldom but it did.

 

[Domenic]

It's probably a timing issue. Try adding DoEvents after you copy, and doing the paste operation in a separate procedure, and then try pausing the macro every nth iteration. Hopefully all of this will help.

So, for example, first add the following procedures to your project...

Sub PastePictureInWorkbook(ByVal targetCell As Range)

'   ---------------------------------------------------------------------
        sStepID = "6.d. putting picture of data into center workbook"
'   ---------------------------------------------------------------------
        
'       Paste picture into the workbook. Crash about 5% of the time.
        Set oPicture = targetCell.Parent.Pictures.Paste

'       Make the picture opaque.
        With oPicture
            .Left = targetCell.Left
            .Top = targetCell.Top
            With .ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With
            
        End With
        
        DoEvents
        
End Sub

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
    
    Do
        DoEvents
    Loop Until Timer > endTime
    
End Sub

Then amend your code as follows...

'   ----------------------------------------------------------
        sStepID = "6.c. making picture of data for a center"
'   ----------------------------------------------------------

'       Update values in grand total line to include sum of visible rows
'       (for the center being processed).
        Application.Calculate

'       Create a picture of the data for the center.
        rAllRawData.Copy
        
        DoEvents

        PastePictureInWorkbook wbSSCD_CS_IRAA.Names("AnchorCell").RefersToRange
        
'       Pause the macro for 3 seconds every 5th iteration (change the seconds and nth iteration as desired)
        If (loopCounter - 1 Mod 5) + 1 = 5 Then PauseMacro 3

Hope this helps!

 

[jolivanes]

I have had good luck with using the duplicate statement.
The original can be deleted later on if need be.

Sub Maybe()
Sheets("Sheet2").Shapes("Picture 1").Duplicate.Cut
Sheets("Sheet3").Select
Range("C5").PasteSpecial
End Sub

 

[OaklandJim]

Domenic. I really appreciate your idea. I just delayed 2 seconds on each iteration as the issue is so intermittent. Increases sub's operation time by about 40 seconds (2 seconds times 20 iterations) which would be ok if it worked. Unfortunately, that suggestion did not work.

If anyone cares, I have a work-around. I do not like it but it works.

Workaround: Code looks in a "starting center" cell in the master workbook for the first center to process. As iterations happen I keep track of which centers have been processed. In the error handler I put that number (of centers processed) plus one into the "starting center" cell in the master workbook before showing an error message. That way code can be restarted to process the centers starting with that center number.

Clunky for sure. But that is the only way I could think of to handle the issue.

 

[Domenic]

Did you try it the way I showed you in my post where it includes DoEvents, seperation of copy and paste operations, and pausing the macro for a few seconds every nth iteration?

 

[OaklandJim]

Jolivanes. Thanks for the idea. I cannot quite make out how to use your idea. After COPYING THE RANGE in the data workbook (Worksheets(1)) what do I do? As it is now, after copying the RANGE in the data workbook, the cell in the target workbook where the picture is to be pasted (in Worksheets(1)) is activated then the paste is done.

 

[OaklandJim]

Domenic. OK I re-looked-at your code snippit and the way I implemented your idea seems to work. I tried several times and no error. BUT, now, instead of the "master" workbook and sheet staying "on top" of what is shown to user, now the "target" workbook and sheet come to the front and the "master" workbook and worksheet are underneath. So what user sees is the master, then the target, the master then the target, etc. twenty or more times. I would prefer that that does not happen. Here is what I did based on your suggestion:

        '   ----------------------------------------------------------
        sStepID = "6.c. making picture of data for a center"
'   ----------------------------------------------------------

'       Update values in grand total line to include sum of visible rows
'       (for the center being processed).
        Application.Calculate

'       Create a picture of the data for the center by copying data in the
'       range where centers' data resides (only one center's rows are exposed)
'       in the "data" workbook, Workbooks(1).
        rAllRawData.Copy

        DoEvents

'   ---------------------------------------------------------------------
        sStepID = "6.d. putting picture of data into center workbook"
'   ---------------------------------------------------------------------

'       Activate "target" workbook, first worksheet.
        wbSSCD_CS_IRAA.Activate
       
'       Activate 1st (anchor) cell in the header range in the target
'       worksheet as target location for paste.
        Worksheets(1).Range("AnchorCell").Activate
        
'       Paste picture into the cell in the target workbook.
        Set oPicture = ActiveSheet.Pictures.Paste
        
'       Make the picture opaque.
        With oPicture.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
        End With
        
'       Sub that does delay for specified # of seconds.
        Call PauseMacro(2)

And the delay sub is exactly as you posted.

 

[jolivanes]

Just something to try to see if you get errors. So far, I have not.
Change all references as required.

Sub Copy_Paste_As_Picture()
    Range("A5:H15").CopyPicture Appearance:=xlPrinter, Format:=xlPicture
    ActiveSheet.Range("K13").PasteSpecial
    With Selection
        .Name = "Look_At_This_Mamma"
    End With
    ActiveSheet.Shapes("Look_At_This_Mamma").Duplicate.Cut
    Sheets("Sheet3").Select
    Range("C5").PasteSpecial
End Sub

 

[Domenic]

That's because your code includes these two lines...

wbSSCD_CS_IRAA.Activate

and

 Worksheets(1).Range("AnchorCell").Activate

However, there's no need to do any activating. With the code I posted, you'll see that there's no activating taking place. Have you tried it? Or maybe you don't like the idea of separating the paste operation into a separate function? If not, that's fine. It may not be absolutely necessary (although, according to an experienced user, it seems to help). But you can still use the same method to avoid Activate without placing the code into a separate procedure.

By the way, if you haven't already done so, you can set Application.ScreenUpdating to False at the beginning of your macro, and then set it back to True at the end to improve efficiency.