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 and that I thought was ready to show to client -- I get an intermittent error. Sometimes 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 makes a picture of the data (in the "data" workbook) by copying the range of data for the center being processed. The picture is pasted back into that same ("data") workbook so I can format it (e.g. so it is opaque).

That picture is then cut from the "data" workbook then pasted into the "target" workbook, into the cell specified by code.

Code crashes at different times. More specifically code might do what is specified for some of the centers and not others. Sometimes code actually processes all centers as expected.

Mostly code seems to choke when I do the initial paste of the picture back into the "data" workbook.

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

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

'       Activate the raw data workbook to set up for the copy
'       & paste of raw data for the center being processed.
        wbRawDataWorkbook.Activate

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

'       Paste the picture back into the data worksheet, for processing.
        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

'       Cut the picture made of center's data to prepare
'       for paste into SSCD-CS-IRAA workbook with center's data.
        oPicture.Cut

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

        wbSSCD_CS_IRAA.Activate
        
'       Activate 1st (anchor) cell in the header range as target for paste.
        Range("AnchorCell").Activate
        
'       Put picture into the workbook.
        ActiveSheet.Pictures.Paste

Is someone willing to give my workbooks/code a look over to see what might be causing the error and to help me know what to do about it? I would send the "master", "data" and "template" workbooks via email so you could try running it.

I know that rules forbid "off-line" discussion via email but the code is way too much to post. I promise to provide an answer if I get one.

 

[OaklandJim]

Update. This seems to be working. I used a different approach to getting the picture into the "target" workbook. Specifically I eliminated the extra paste then cut into/from the "data" workbook (where the picture comes from) using code below. Now code pastes the original picture (from the "data" workbook) DIRECTLY into the "target" workbook.

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

'       Activate the raw data workbook to set up for the copy
'       & paste of raw data for the center being processed.
        wbRawDataWorkbook.Activate

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

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

        wbSSCD_CS_IRAA.Activate
       
'       Activate 1st (anchor) cell in the header range as target for paste.
        Range("AnchorCell").Activate
       
'       Paste picture into the 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