You may notice that Excel’s Clipboard data has been changed (or purged) after using one of its many copy methods. Then again, this issue may have slipped past you while testing your code. This situation, though annoying, is quite understandable.

The Problem…

In many of my Excel VBA macros, rows are added using the Range Copy method, as shown in the following code snippet:

    NewRow = Sheet1.Range("Q2") + 1
    newLastLine = (NewRow + numLines) - 1
    
    Sheets("BidSheet_Template").Rows(17).Copy

In this example code, the new rows are copied from a hidden “template” worksheet. The variable NewRow is set to the next row down from the ActiveCell.Row, which is saved in a hidden column’s cell, “Q2”. For this workbook, every time a user moves to another cell Sheet1.Range (“Q2”) is updated. The variable newLastLine is used to account for inserting multiple rows by adding the value of the variable numLines. That variable comes from the form, which allows the user to select adding 1 or multiple new rows. It is the number of lines to add.

So much for the really inconsequential code. What matters comes next, the Copy method. Many times a user will copy some text and want to insert it in a new row, I know that I do. The issue here is that once the VBA Copy method is called, the clipboard overwrites the previous data with the “copied” data. In the previous code, the copied row(s).

The Fix…

The only way I have found to overcome this problem is indeed simple. It does not use complex calls to Windows APIs as some websites suggest. To use the following method you must have the Microsoft Forms 2.0 Object library (FM20.dll) referenced in the project.

Because my only concern was keeping any String-type Clipboard data, I save the Clipboard’s contents to a string. To be safe or to capture data other than simple strings, you could use VBA’s Variant type.

I fixed this problem by creating 2 short routines. The first one is a User Defined Function (UDF) that sets a variable to the current clipboard data, Clipboard_GetText():

Public Function Clipboard_GetText()
' Save the value in the clipboard to restore after a "copy" routine...

Dim objData As DataObject

    Set objData = New DataObject
    
    objData.GetFromClipboard
    Clipboard_GetText = objData.GetText

    Set objData = Nothing

End Function

The second routine is a Sub procedure, Clipboard_SetText that simply sets the current clipboard data to the variable saved using the function. It takes the saved variable as an argument and sets it to the contents of the Clipboard:

Public Sub Clipboard_SetText(SavedText As String)
' Set the Clipboard text to the value of the String argument, to be used after the function Clipboard_GetText()

Dim objData As DataObject
    
    Set objData = New DataObject
    
    objData.SetText SavedText
    objData.PutInClipboard

    Set objData = Nothing

End Sub

The Implementation…

Once you have the Clipboard_GetText function and the Clipboard_SetText sub-procedure coded, the application is easy. This code works whether a user has copied anything to the Clipboard or not. It can be included, just in case a user wants to paste copied text. The four steps are:

• Declare a String variable, named however you wish (the following example uses the name “strSavedClip”)
• Use the function to Set the String variable to the current Clipboard data
• Run your “copy” code…
• Call the subroutine to reset the Clipboard data to the saved value

This is how the code should be arranged…your code will look somewhat different, especially since the 2 Calls often may not appear next to each other.

' Save the current clipboard data
strSavedClip = Clipboard_GetText()

'---> ADD your copying code here

' Reset the saved clipboard data
 Call Clipboard_SetText(strSavedClip)

This is a simple solution that can be used over and over again throughout your project.

Remember that, using “Application.CutCopyMode = False” will also clear Excel’s recently copied data from the Clipboard. You should avoid setting this property when possible.