Excel VBA Clipboard error

[jdc2000]

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:

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.

[Zvoni]

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?

[jdc2000]

Sorry, typo, 32 bit.

Clipboard History is Off.

[Zvoni]

Clipboard History is Off.

Eh?
turn that one on

[Dan_W]

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

[Zvoni]

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

[jdc2000]

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.

[wqweto]

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>

[jdc2000]

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.

[jdc2000]

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.

[jdc2000]

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.

[johnfixesstuff]

I believe I have some insight on this error and have experienced it myself (if your error is the same as mine, screenshot below). When our company was sold and we went from Windows 10 to Windows 11, my code, which never got this error previously, and ran fine, began giving me this error. It is not bogus, it is just that the error means that Excel is trying to make Excel's internal clipboard available to non-Excel programs (via the Windows universal clipboard), but for some reason VBA finds that windows clipboard is unavailable for its use, because it is in use by another program. If you were to go to another program outside of Excel, the data you just copied would, in fact, be unavailable (but before the error it was available). That is why it seems to not be affecting you in what you are doing in VBA--it only involves the clipboard contents availability to other Windows applications, which VBA doesn't need to do what it is doing.

[jdc2000]

Thanks for the update on this. This is happening on my Windows 10 systems also. Your screen capture is not visible. These forums have a problem with attachments. In order for them to actually work, you need to click on Go Advanced when posting them and add attachments from that screen.

Even if it is an issue between the internal Excel clipboard when trying to copy to the Windows clipboard, this should still not be happening. When I run these macros, there is nothing else on the computer that should be using the Windows clipboard. The macros also display a warning that the Windows Clipboard will not be available while the macros are running. However, it is possible that one of or more the IT security programs is interfering with the Windows Clipboard, and causing the issue. It is also possible that this is a Microsoft (Office) 365 issue.

I have now added the copy verification code to all of the macros that use this method for copying data, and have not seen any errors except for cases where the data actually could not be copied due to either exceeding cell string size limits, or illegal characters present in the data. So, unless there is something interfering with the copy operation, this message should not appear. It would be nice to figure out where the interference is coming from however.

[johnfixesstuff]

Here's a second try at attaching the error screenshot, using the advanced edit window:

[jdc2000]

That is the error message that I am getting. Thanks.