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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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.
- 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.
- Secondly, you will see the VBA window after selecting the previous command.
- Here, from the Insert tab, select Module.
- 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
- 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.
- 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")
- 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
- First of all, name the sub-procedure as 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
- 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.
- Afterward, a msgbox with the copied text will pop up to notify us that the process has been completed.
- 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.
- 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.
- Firstly, I will take the following data set and select the text in cell B5 for copying.
- 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
- Firstly, name the sub-procedure as 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.
- Then, place the data object’s text into the clipboard.
- After that, the msgbox will notify you that the text has been copied.
MsgBox "The text has been copied!", vbInformation
- Thirdly, keeping the cursor in the module run the code after saving it.
- Fourthly, you will see that the copy has been done successfully.
- Finally, to see the copied text, go to the Clipboard group of the Home tab just like in the previous procedure.
- 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.
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.