none
VBA .PasteSpecial Error RRS feed

  • Question

  • I have this code which attempts to copy and paste a picture. It gives a sporadic (works error free about 3 / 10 times) error 1004, "PasteSpecial method of Range class failed". Often, if I just debug and click the "play" button to continue the code, it pastes just fine (although this only works sometimes. Others it gets hung up)
    I followed EvoAndy and Siddharth Rout's exchange and tried the DoEvents solution, but it did not work: https://social.technet.microsoft.com/Forums/en-US/4855e0ea-6dfd-48a5-8d9e-82a384781f98/vba-pastespecial-error?forum=exceldev 

    Other forums investigating this same issue say to have the code pause so that the Copy method has time to finish, and refer to Chip Pearson's suggestion of Application.Wait  http://www.cpearson.com/Excel/WaitFunctions.aspx This also did not work. 

    Any help would be greatly appreciated! Thank you!

            Dim i, j        As Long
            Dim Arr(2, 1)   As Long
            Dim sVal        As String
            Dim sInitials   As String
            
            Dim rngQual     As Range
            
            Call ToggleAppSettings(False)
            'array of row,col pairs representing the paste destination in Range("rptCertSignatures")
            Arr(0, 0) = 1
            Arr(0, 1) = 1
            Arr(1, 0) = 1
            Arr(1, 1) = 3
            Arr(2, 0) = 6
            Arr(2, 1) = 1
            
            With Range("Appraisers123")
                For i = 1 To .Cells.Count
                    sVal = .Cells(i, 1).Value
                    With wsCert.Range("rptCertSignatures").Cells(Arr(i - 1, 0), Arr(i - 1, 1))
                        Call DeleteShapesInRange(.Cells(1, 1))
                        Set rngQual = wsQualifications.Range("Qual" & i & "Anchor")
                        'have to unhide or pics won't paste into correct spots
                        Call ToggleShowHideFormat(wsQualifications.Range("rptQual" & i), False)
                        Call DeleteShapesInRange(rngQual)
                        If sVal <> "" Then
                            'copy/paste qualification
                            wsAppraisers.Shapes(sInitials & "Qualification").Copy
                            DoEvents
                            On Error Resume Next
                            rngQual.PasteSpecial
                            While Err.Number <> 0 And j < 10
                                Err.Clear
                                Application.CutCopyMode = False
                                wsAppraisers.Shapes(sInitials & "Qualification").Copy
                                DoEvents
                                Application.Wait Now + TimeSerial(0, 0, 1)
                                DoEvents
                                rngQual.PasteSpecial
                                j = j + 1
                                Debug.Print "paste " & j & ", err = " & Err.Number
                            Wend
                            On Error GoTo 0
                        End If
                    End With
                Next
            End With
            Call ToggleShowHideFormat(wsCert.Range("rptCertification"), True)
            
            Application.CutCopyMode = False
            Target.Parent.Activate
            Call ToggleAppSettings(True, True)
            
            Set rngQual = Nothing


    Wednesday, January 31, 2018 5:44 PM

Answers

  •  Also posted "solution" here: https://www.mrexcel.com/forum/excel-questions/1041613-vba-pastespecial-error-image.html#post4999872  

    I've figured out a band-aid, yet repeatable solution. I closed out all my applications except Excel: Access, Outlook, Spotify, and a Chrome browser with 3 tabs open. The problem went away and works 100% of the time with no Debug.Print, so it never went into the error loop.
    I then opened up 2 excel instances with 2 workbooks each, Outlook, Spotify, two windows explorer folders, an Access DB, and a Chrome browser with 10 tabs.  The same sporadic problem came back. Worked sometimes, others not, and others it went into the error loop and succeeded after multiple attempts.
    Finally, I closed out everything except Outlook, Spotify, just one excel instance with the workbook i'm testing, and 1 Chrome browser with 2 tabs, and not it works 100% of the time with no Debug.Print.

    Thus, it seems there is some threshold resource level above which the clipboard behaves erratically and/or needs alot of time to perform the copy operation. I suppose this makes sense since the computer itself runs slower when many apps are open, just hadn't encountered anything like this before.
    Is there a better way to handle this operation since I won't necessarily know how many resources will be available on the users' machines?  Something like increase the Application.Wait time as the % CPU resources available goes down?  That seems possible with an API, but my gut tells me 'probably don't want to over-complicate an issue that is resolved by closing extraneous applications."
    Any input is appreciated, although I'm good to go for the time being! Thanks!

    • Marked as answer by Merce33 Thursday, February 1, 2018 10:28 PM
    Thursday, February 1, 2018 10:27 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, February 1, 2018 2:18 AM
  • Hello. Due to no responses, I have posted this also here. If answered, I will update this thread to indicate that it is answered and close this post.
    https://www.mrexcel.com/forum/excel-questions/1041613-vba-pastespecial-error-image.html#post4999872 
    • Edited by Merce33 Thursday, February 1, 2018 9:32 PM
    Thursday, February 1, 2018 9:21 PM
  •  Also posted "solution" here: https://www.mrexcel.com/forum/excel-questions/1041613-vba-pastespecial-error-image.html#post4999872  

    I've figured out a band-aid, yet repeatable solution. I closed out all my applications except Excel: Access, Outlook, Spotify, and a Chrome browser with 3 tabs open. The problem went away and works 100% of the time with no Debug.Print, so it never went into the error loop.
    I then opened up 2 excel instances with 2 workbooks each, Outlook, Spotify, two windows explorer folders, an Access DB, and a Chrome browser with 10 tabs.  The same sporadic problem came back. Worked sometimes, others not, and others it went into the error loop and succeeded after multiple attempts.
    Finally, I closed out everything except Outlook, Spotify, just one excel instance with the workbook i'm testing, and 1 Chrome browser with 2 tabs, and not it works 100% of the time with no Debug.Print.

    Thus, it seems there is some threshold resource level above which the clipboard behaves erratically and/or needs alot of time to perform the copy operation. I suppose this makes sense since the computer itself runs slower when many apps are open, just hadn't encountered anything like this before.
    Is there a better way to handle this operation since I won't necessarily know how many resources will be available on the users' machines?  Something like increase the Application.Wait time as the % CPU resources available goes down?  That seems possible with an API, but my gut tells me 'probably don't want to over-complicate an issue that is resolved by closing extraneous applications."
    Any input is appreciated, although I'm good to go for the time being! Thanks!

    • Marked as answer by Merce33 Thursday, February 1, 2018 10:28 PM
    Thursday, February 1, 2018 10:27 PM