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?

 

[OaklandJim]

Hello Dominic I really appreciate all of your assistance. You've already helped me a lot. The first go 'round I rushed. I only picked up on the "wait" sub that you provided and did not realize that you provided two subs.

Anyway, I THINK that I did what you suggested. I'm not certain but that seemed to have improved the crash-on-paste issue but it still happens, but less often.

Side note: When I was doing original code I could not figure out how to paste picture where I wanted without activating the target cell first. As you point out that is unnecesary.

BUT, I still get that onscreen back-and-forth between master workbook and the newly created target workbook. It is ugly! What is shown is what I'll call an "empty" worksheet, no row or column headers, no grid lines, just gray. I sure wish that I could get rid of that back-and-forth for client. Oy.

When code in sub named Create_SSCD_CS_IRAA_Workbook (call is shown in next snippit) creates that target workbook (from a template file) the newly created workbook is the active one no, just like activating it? Here is how the sub that creates the target workbook is coded.

'   -------------------------------------------
        sStepID = "3. opening template file"
'   -------------------------------------------
    
'   Open the template file.
    Application.ScreenUpdating = False
    
    Set wbTemplate = Workbooks.Open(Filename:=sTemplatePath & sTemplateFileName)

'   -------------------------------------------------------------
        sStepID = "4. saving newly created SSCD_CS_IRAA file"
'   -------------------------------------------------------------
    
'   Create target workbook using save as of the template workbook.
    Application.DisplayAlerts = False
    wbTemplate.SaveAs sFileToSaveNameAndPath

Here is the code in the main sub that includes your original suggested approach.

'       Make center-specific/date-specific SSCD-CS-IRAA.xlsx workbook.
'       The newly created workbook is now the active one.
        Call Create_SSCD_CS_IRAA_Workbook( _
            sCenterToProcess, _
            iCenterRowsCount, _
            sDateForFileName, _
            wbSSCD_CS_IRAA)
                            
        ThisWorkbook.Activate
'   ----------------------------------------------------------
        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"
'   ---------------------------------------------------------------------

        Application.ScreenUpdating = False

        Call PastePictureInWorkbook(wbSSCD_CS_IRAA.Worksheets(1).Range("AnchorCell"))

'       Sub that does delay for specified # of seconds.
        Call PauseMacro(2)

That sub (snippit above) calls this one based on your code

Sub PastePictureInWorkbook(ByVal prTargetCell As Range)

    Dim oPicture As Picture

'   Paste picture into the workbook. Crash about 5% of the time.
    Set oPicture = prTargetCell.Parent.Pictures.Paste

'   Make the picture opaque.
    With oPicture
        .Left = prTargetCell.Left
        .Top = prTargetCell.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

 

[OaklandJim]

Attached is what user sees after creation of a target workbook.

 

[jolivanes]

Just playing around with what I perceive to be your problem.
Ran this without problems. No errors or freezing. Of course it could be different under different circumstances.
I do not like selecting and activating but when pictures/shapes are involved, I find it works better (in Excel 2007 anyway).

Sub Copy_Paste_As_Picture_30_Times()
Dim shName As String, i As Long, j As Long
shName = ThisWorkbook.ActiveSheet.Name
i = 1
j = 5
Application.ScreenUpdating = False
Do Until i = 31
    Range("A" & i).Resize(5, 8).CopyPicture Appearance:=xlPrinter, Format:=xlPicture
    ActiveSheet.Range("K13").PasteSpecial
    With Selection
        .Name = "Look_At_This_Mamma" & i
    End With
    ActiveSheet.Shapes("Look_At_This_Mamma" & i).Duplicate.Cut
    
    Workbooks("Book22222.xlsm").Activate
        Sheets("Sheet3").Select
            Range("C" & j).PasteSpecial
                ThisWorkbook.Activate
            Sheets(shName).Shapes("Look_At_This_Mamma" & i).Delete
        i = i + 1
    j = j + 5
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Domenic]

. . . the newly created workbook is the active one no, just like activating it?

Yes.

Probably no need to ThisWorkbook.Activate. This will slow things down.

And Application.Calculate calculates all open workbooks, not just the active one. If this is not the intent, this too will slow things down.

Can you post your complete code, except PastePictureInWorkbook and PauseMacro?

 

[OaklandJim]

Domenic. Oddly the back-and-forth between master and target workbooks only happens when I use your approach with the two subs and the delay. Seems inexplicable because I adapted your code but within my main sub...no the delay. Same syntax, different results. Ahh Excel. I do think that DoEvents helped the paste picture crash issue. But Excel still craps out now and then. I think that my error handler approach to a workaround 'll probably do.

I was trying to keep the master workbook on top so I tried ThisWorkbook.Activate. I'll take it out. I need to calculate. to get the result from formulas. I'll calculate the respective worksheet only?

I might explore jolivanes' approach or respond to your latest quesry tomorrow. Today I am done with Excel.

 

[Domenic]

I might explore jolivanes' approach or respond to your latest quesry tomorrow. Today I am done with Excel.

That's understandable...

 

[OaklandJim]

Dominic. I already said this but I really appreciate your help. My new code is definitely cleaner and tighter and probably a bit faster.

Showing all the code is too much. Here is the entire code block that does all the processing of "centers" including copying and pasting the picture. The code before it deals with declarations and initializations plus preliminaries like finding and opening the raw data sheet, messaging, etc. If you want error handler function I could show that too but it is one I've used many times so I know that it isn't borken. As you've seen, I write lots of comments and use long var names. I do that because if I look at the code a month later I'll forget stuff. notice that I used your code in my sub.

FWIW the DoEvents seem to be hleping. It SEEMS that there are fewer crashes.

'   --------------------------------------------------
        sStepID = "6. processing (looping) centers"
'   --------------------------------------------------

'   Show "persisitent" progress indicator form.
    CentersProgressDialog.Show vbModeless
    
'   Put count of centers to process into the form (label).
    CentersProgressDialog.LabelCentersCount.Caption = iCentersCount
    
'   Get user date from [Main] sheet in this workbook.
    vDateOfRecord = [Main].Range("DateOfRecord").Value
    
    For iCenterIndex = iFirstCenterToProcess To iCentersCount
    
'       Clear clipboard so it does not get filled up.
        clipboard.Clear

'       Put center # into the progress indicator form (label).
        CentersProgressDialog.LabelCenterNumber.Caption = iCenterIndex
        DoEvents

'       Keep track of how many rows for the center being processed.
        iCenterRowsCount = 0
            
'   --------------------------------------------------------------
        sStepID = "6.a. hiding rows for non qualifying centers"
'   --------------------------------------------------------------

'       Unhide all raw data rows before hiding rows whose content is not
'       for the center being processsed (below).
        rAllRawData.EntireRow.Hidden = False

'       Get center from array holding all centers' names.
        sCenterToProcess = asCenters(iCenterIndex)

'       Loop all center rows. Hide those whose value <> sCenterToProcess.
        For Each rCellCenter In rCenters
            If rCellCenter.Value <> sCenterToProcess _
             Then
                rCellCenter.EntireRow.Hidden = True
            Else
                iCenterRowsCount = iCenterRowsCount + 1
            End If

        Next rCellCenter
        
'   ----------------------------------------------------------
        sStepID = "6.b. creating workbook for a center"
'   ----------------------------------------------------------

'       Get date for name of the date-specific SSCD-CS-IRAA.xlsx workbook.
        sDateForFileName = Format(vDateOfRecord, "yyyy.mm.dd")
        
'       Make center-specific/date-specific SSCD-CS-IRAA.xlsx workbook.
        Call Create_SSCD_CS_IRAA_Workbook( _
            sCenterToProcess, _
            iCenterRowsCount, _
            sDateForFileName, _
            wbSSCD_CS_IRAA)
                    
'       Point anchor cell object to the anchor cell in the new target
'       workbook whose by ref parameter wbSSCD_CS_IRAA is set by call
'       to sub Create_SSCD_CS_IRAA_Workbook above. That sub also adds
'       name "Anchor Cell" for that cell.
        Set rTargetAnchorCell = wbSSCD_CS_IRAA.Worksheets(1).Range("AnchorCell")
        
'   ----------------------------------------------------------
        sStepID = "6.c. making picture of data for a center"
'   ----------------------------------------------------------

'       Update values in grand total line to include sum of visible rows.
'       There are formulas in cells in grand total row.
        wsRawDataSheet.Calculate

'       Create a picture of the data for the center. Although copying
'       "all data rows", only rows for the center being processed are visible.
        rAllRawData.Copy
        
        DoEvents
        
'   ---------------------------------------------------------------------
        sStepID = "6.d. putting picture of data into center workbook"
'   ---------------------------------------------------------------------

'       Paste picture into the target worksheet in the target workbook
'       (i.e., worksheet 1 in wbSSCD_CS_IRAA). Refer to the worksheet
'       as the parent of rTargetAnchorCell.
        Set oPicture = rTargetAnchorCell.Parent.Pictures.Paste
        
'       Place the picture into anchor cell. Make the picture opaque.
        With oPicture
            .Left = rTargetAnchorCell.Left
            .Top = rTargetAnchorCell.Top
            With .ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With
        End With

'       Activate 12th column in header range. Do so that cell is the active
'       cell -- and the cell that user sees -- when they open the target
'       workbook/worksheet for use.
        rTargetAnchorCell.Offset(0, 12).Activate
        
        DoEvents
        
'   ---------------------------------------------------------------------
        sStepID = "6.e. saving and closing center-specific workbook"
'   ---------------------------------------------------------------------

        wbSSCD_CS_IRAA.Save
        wbSSCD_CS_IRAA.Close
        
    Next iCenterIndex

'   ----------------------------------
        sStepID = "7. closing out"
'   ----------------------------------
    
    CentersProgressDialog.Hide
    DoEvents
    
    Application.ScreenUpdating = False

    Application.CutCopyMode = False
        
'   Expose all rows in raw data when done processing Centers
    rAllRawData.EntireRow.Hidden = False
    
'   Set user on an empty cell in thisworkbook.
    ThisWorkbook.Activate
    [Main].Range("C4").Select
    
'   Set "first center to process" in Main worksheet to n/a.
    [Main].Range("First_Center_to_Process") = "n/a"
    
    gsMsg = "Done processing centers " & iFirstCenterToProcess & " to " & iCentersCount & "."
    
    MsgBox gsMsg, vbInformation, gsTitle
    
    Application.EnableCancelKey = xlInterrupt

Exit Sub

ErrHandler:

'   Kill the progress indicator.
    CentersProgressDialog.Hide
    DoEvents

'   Show error message.
    Call ErrorMessage(Err.Number, Err.Description, sSubName, sStepID)

    If iCenterIndex <= iCentersCount _
     Then
            gsMsg = "Processed " & iCenterIndex - 1 & " of " & iCentersCount & " centers."
            gsTitle = "Processing centers."
            MsgBox gsMsg, vbInformation, gsTitle
            
'           Put the index of the last center processed into cell whose
'           value indicates the first enter to process when code is restarted.
            [Main].Range("First_Center_to_Process") = iCenterIndex
            
'           Close any open SSCD_CS_IRAA_files.
            Call Close_SSCD_CS_IRAA_Files

    End If

End Sub

 

[Domenic]

I don't really have any other suggestions, other than maybe increasing the number of seconds you pause the macro from 2 to 3 or 5 seconds. Sure it may take it bit longer, but if it eliminates crashes entirely it would be worth the extra tme. And you could take advantage of that time by having yourself a coffee o tea.

Cheers!