Results 1 to 11 of 11

Thread: Excel VBA Clipboard error

  1. #1

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Excel VBA Clipboard error

    I have an Excel workbook with macros that copy data from various sheets to cells on a main sheet. These macros have worked flawlessly for years, however, today I find that I am getting the following error message:

    We couldn't copy the content to the clipboard because it's in use by another application.
    You can still paste your content within this workbook, but it won't be available in other applications.

    The code that is used to copy the data is below:

    Code:
    Application.ScreenUpdating = False
    
    Sheet2.Range("I1:O1").Copy
    Sheet1.Range("L1:R1").PasteSpecial Paste:=xlPasteAll
    Sheet1.Range("L1:R1").PasteSpecial Paste:=xlPasteColumnWidths
    
    Application.CutCopyMode = False
    
    For lngRow2 = 2 To lngLastRowSheet2
        strFileName = CStr(Sheet2.Cells(lngRow2, 6))
        lngTemp1 = 0
        For lngRow1 = 2 To lngLastRowSheet1
            If lngTemp1 = 0 Then
                strTemp1 = CStr(Sheet1.Cells(lngRow1, 6))
                If strTemp1 = strFileName Then
                    lngTemp1 = lngRow1
                End If
            End If
        Next lngRow1
        If lngTemp1 <> 0 Then
            strRow1 = Trim(CStr(lngTemp1))
            strRow2 = Trim(CStr(lngRow2))
            Sheet2.Range("I" & strRow2 & ":O" & strRow2).Copy
            Sheet1.Range("L" & strRow1 & ":R" & strRow1).PasteSpecial Paste:=xlPasteAll
            Application.CutCopyMode = False
        End If
        Application.StatusBar = "CStr(lngRow2) & " Rows processed, Sheet2 Data"
        DoEvents
    Next lngRow2
    
    Application.ScreenUpdating = True
    The macro code still works OK on one computer with Excel 2016 16 bit, however, on another computer with Excel 365 MSO (Version 2401 Build 16.0.17231.20236) 32 bit, I get the error message.

    No other applications programs are running, except of course for all of Microsoft's Windows 10 background garbage plus whatever applications that the the company IT personnel have running for security.

    Any ideas on what to do to eliminate this error would be appreciated. I have already unchecked "Enable Live Preview" in the Options for Excel.

    Thanks.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,525

    Re: Excel VBA Clipboard error

    Quote Originally Posted by jdc2000 View Post
    The macro code still works OK on one computer with Excel 2016 16 bit, however, on another computer with Excel 365 MSO (Version 2401 Build 16.0.17231.20236) 32 bit, I get the error message.

    No other applications programs are running, except of course for all of Microsoft's Windows 10 background garbage plus whatever applications that the the company IT personnel have running for security.

    Any ideas on what to do to eliminate this error would be appreciated. I have already unchecked "Enable Live Preview" in the Options for Excel.

    Thanks.
    Really??

    as to the question: Is the Clipboard-History on or off?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: Excel VBA Clipboard error

    Sorry, typo, 32 bit.

    Clipboard History is Off.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,525

    Re: Excel VBA Clipboard error

    Quote Originally Posted by jdc2000 View Post
    Clipboard History is Off.
    Eh?
    turn that one on
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    Lively Member
    Join Date
    May 2021
    Posts
    96

    Re: Excel VBA Clipboard error

    It's not an error message I've ever seen so I'm not entirely sure, but I tend to find that any issues with the clipboard tend to 'fix' themselves when you force VBA to pause before proceeding with the paste method. After executing the copy method, I would ordinarily call a pause routine like the one below (apologies for not formatting it, but I keep getting a timeout message from the server for some reason).

    That said, is it really necessary to use the clipboard at all? I tend to prefer just assigning the values from one range to another to move data around. I note that there is a columnwidths pasting going on, so I assume that it's more advanced than just values, but I'd almost be inclined to adjust column widths via code as well than relying on the clipboard (which is always hit and miss).

    Sub Pause(Optional ByVal Period As Single = 1)
    Period = Timer + Period
    Do
    DoEvents
    Loop Until Period < Timer
    End Sub

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,525

    Re: Excel VBA Clipboard error

    Quote Originally Posted by Dan_W View Post
    That said, is it really necessary to use the clipboard at all? I tend to prefer just assigning the values from one range to another to move data around. I note that there is a columnwidths pasting going on, so I assume that it's more advanced than just values, but I'd almost be inclined to adjust column widths via code as well than relying on the clipboard (which is always hit and miss).
    "xlPasteAll" is literally "paste all": backcolor, Forecolor, Bold, Italic blablabla
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: Excel VBA Clipboard error

    Thanks, Zvoni.

    Clipboard History is normally Off for security reasons. I will attempt to turn it on and run a test to see what happens, but that may not be a setting that I can change. I will test and report back.

    Dan_W,

    I normally do just copy the data values without using the Clipboard, however, in this case, I need to copy the formatting and other non-data values as well. I am using a DoEvents to allow a progress message to be displayed on the Status bar, and to prevent the macros from locking up Excel and/or Windows. Without the the DoEvents, the Status message would never display and Excel and eventually Windows would eventually lock up. These macros operate on 18 sheets of data, some of which contain between 56,000 and 156,000 rows of data, and they take 36-48 hours to fully process all of that data. The macros work fine on one of my computers without adding any delays, which would significantly increase the time for them to complete, so I suspect that IT or Microsoft has done something to the computer where I am getting the error message.

    Thanks.

  8. #8
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,230

    Re: Excel VBA Clipboard error

    Quote Originally Posted by jdc2000 View Post
    Sorry, typo, 32 bit.

    Clipboard History is Off.
    Btw, try pressing Win+V -- if it lists several past clipboard entries then clip history is not off.

    cheers,
    </wqw>

  9. #9

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: Excel VBA Clipboard error

    Win+v shows "Can't show Clipboard History", so it is definitely off. I will try enabling it and, if successful, run a test to see what happens.

  10. #10

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: Excel VBA Clipboard error

    OK, I was able to activate the Clipboard History, however, I still got the same error at about the same point during the process.

    Note that the computer where the macros process works OK has the Clipboard History turned off.

  11. #11

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,404

    Re: Excel VBA Clipboard error

    Update:

    I have added some code to my macros that verifies that the data that was supposed to be copied from the source sheet to the destination sheet using the Clipboard was actually copied. I have only added the verification code to 3 of the 18 copy macros so far, but during the testing I have seen the error message pop up but no error message box has appeared from the verification code, so it looks like the Excel Clipboard Copy error message box is bogus. I will update again after I have added the verification code to all remaining copy macros and run a complete test. I will have to run a full test over the weekend, but that should also give me an idea of how much additional time the full verification code adds to the process.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width