How to Copy Text to Clipboard Using VBA in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Many times users need to copy text or data for working purposes in Microsoft Excel. Using a macro code or VBA is one of the best solutions to copy text data. But the copied data in the macro may get lost if the macro has stopped running. Storing the copied data in the clipboard in Excel can solve this problem. In this article, I will show you how to copy text to a clipboard using VBA in Excel.

Table of Contents hide

Download Practice Workbook

2 Easy Ways to Copy Text to Clipboard Using VBA in Excel

1. Creating VBA Custom Function to Copy Text to Clipboard

2. Using DataObject Variable to Copy Text to Clipboard

Conclusion

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Copy to Clipboard.xlsm

2 Easy Ways to Copy Text to Clipboard Using VBA in Excel

In this article, you will see two ways to copy text to clipboard using VBA in Excel. Firstly, I will create a custom function and then copy text by using the function in my first method. Secondly, I will use the DataObject variable to do the same as my second procedure.

For both my procedures, I will use the following data set. Here, I have some random text that I will be copying using VBA.

1. Creating VBA Custom Function to Copy Text to Clipboard

For my first method, I will create a custom function using VBA. Then, with the help of this custom function, I will use another VBA code to copy the desired text. To know more about this procedure, see the following steps.

Step 1:

First of all, go to the Developer tab of the ribbon.
Then, from the Code group, select Visual Basic.
Here, I will copy the text in cell B6.

Step 2:

Secondly, you will see the VBA window after selecting the previous command.
Here, from the Insert tab, select Module.

Step 3:

Thirdly, in the module, copy and paste the following code to create the custom function.

'Creating custom function for copying
Function VBA_Copy_to_Clipboard(Optional StoreText As String) As String
'Declaring variable
Dim M As Variant
'Storing variable as variant
M = StoreText
'Create HTMLFile Object
With CreateObject("htmlfile")
With .parentWindow.clipboardData
'Deciding case for copying
Select Case True
'Returning the number of characters using the len function
Case Len(StoreText)
'Writing to the clipboard
.setData "text", M
'If there is no variable
Case Else
'Reading from the clipboard and no variable passed through
VBA_Copy_to_Clipboard = .GetData("text")
End Select
End With
End With
End Function

VBA Breakdown

Firstly, name the custom function as VBA_Copy_to_Clipboard and the function will be stored as a string variable.

Function VBA_Copy_to_Clipboard(Optional StoreText As String) As String

Secondly, declare the variable.

Dim M As Variant

Thirdly, store the variable as a variant.

M = StoreText

Fourthly, create the HTMLFile object.

With CreateObject("htmlfile")
With .parentWindow.clipboardData

Then, decide the case for copying text.

Select Case True

After that, the LEN function will return the number of characters of the text.

Case Len(StoreText)

Then, the text will be written on the clipboard.

.setData "text", M

In case, if there is no variable

'If there is no variable
Case Else

Then, the message box will show an empty result.

VBA_Copy_to_Clipboard = .GetData("text")

Step 4:

Fourthly, copy the second code in the following and paste it into the same module.

'Naming the sub procedure
Sub Excel_Vba_Copy_Text_to_Clipboard()
'Copying the desired text to the clipboard
VBA_Copy_to_Clipboard "Maria needs that book for her exam."
'Reading the copied text in the clipboard
MsgBox VBA_Copy_to_Clipboard
End Sub

VBA Breakdown

First of all, name the sub-procedure as Excel_Vba_Copy_Text_to_Clipboard.

Sub Excel_Vba_Copy_Text_to_Clipboard()

Secondly, use the custom function from the previous code to copy the text.

VBA_Copy_to_Clipboard "Maria needs that book for her exam."

Finally, a msgbox with the desired result will pop up.

MsgBox VBA_Copy_to_Clipboard
End Sub

Step 5:

After that, save the code in the module.
Then, press F5 or the Run button to run the code after keeping the cursor on the module.

Step 6:

Afterward, a msgbox with the copied text will pop up to notify us that the process has been completed.

Step 7:

Then, to see the text in the clipboard, click on the Home tab.
After that, select the little arrow on the lower right side of the Clipboard group.

Step 8:

Finally, you will see the desired text copied to the clipboard.

2. Using DataObject Variable to Copy Text to Clipboard

In my second approach, I will use a DataObject variable to copy text to the clipboard. Unlike the previous method, I will not use a custom function here. Rather, I will store the desired text in a string variable and then put the variable in the clipboard. To learn more about the procedure, follow the below-given steps.

Step 1:

Firstly, I will take the following data set and select the text in cell B5 for copying.

Step 2:

Secondly, open the VBA window from the Developer tab just like the previous method.
Then, paste the following code into the module.

Dim y As String
'Insering text inside the string variable
y = "John will apply for a bank loan."
'Doing object's text equal above string variable
x.SetText y
'Then placing dataobject's text into the clipboard
x.PutInClipboard
'Showing message box
MsgBox "The text has been copied!", vbInformation
End Sub

VBA Breakdown

Firstly, name the sub-procedure as Using_DataObject_Variable.

Sub Using_DataObject_Variable()

Secondly, declare the variables.

Dim x As New DataObject
Dim y As String

Thirdly, insert the desired text inside the string variable.

y = "John will apply for a bank loan."

Fourthly, do the object’s text equal above the string variable.

x.SetText y

Then, place the data object’s text into the clipboard.

x.PutInClipboard

After that, the msgbox will notify you that the text has been copied.

MsgBox "The text has been copied!", vbInformation

Step 3:

Thirdly, keeping the cursor in the module run the code after saving it.

Step 4:

Fourthly, you will see that the copy has been done successfully.

Step 5:

Finally, to see the copied text, go to the Clipboard group of the Home tab just like in the previous procedure.

💡 Notes:

If you face issues in copying text to clipboard in Windows 10 using VBA in Excel, then from the VBA windows go to Tools and then Reference. From there, add the Microsoft Forms 2.0 Object Library as a reference. This may help to solve your issue.
If you want to clear your clipboard after copy-pasting then you may use the following VBA code.

Application.CutCopyMode=False

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to copy text to clipboard using VBA in Excel by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Moreover, you need to keep in mind that the submitted comments need to be approved. Therefore, after commenting, be patient and we will reply to your queries as soon as possible.