Prantick Bala
If you cut or copy any text or image, it will be saved in the clipboard first. After that, you can paste the text or image anywhere such as in Word files and Excel files. In Excel, you can paste from the clipboard in many ways. Using Microsoft Visual Basic Applications (VBA) allows you to paste from the clipboard in your Excel file with some codes. In this article, I’ll show you 3 methods of using VBA to paste from clipboard to Excel.
Suppose, you have copied some texts from a text file and those have been saved in the clipboard. Now, you want to paste it into your Excel file using VBA.
Download Practice Workbook
3 Methods to Paste from Clipboard to Excel Using VBA
1. Paste from Clipboard in Single Cell Using VBA
Before applying this method you need to activate Microsoft Forms 2.0 Object Library for the VBA project. To do that,
➤ Press ALT+F11 to open The VBA
➤ Go to Tools > References in the VBA window.
It will open the Reference- VBAProject window.
➤ Check Microsoft Forms 2.0 Object Library and click on OK.
It will activate the Microsoft Forms 2.0 Object Library. Now,
➤ Click on the Insert tab and select Module.
It will open the Module(Code) window.
➤ Insert the following code in the Module(Code) window,
Sub Paste_from_Clipboard()
Dim CObj As MSForms.DataObject
Set CObj = New MSForms.DataObject
CObj.GetFromClipboard
XText = CObj.GetText(1)
ActiveSheet.Range("B4").Value = XText
End SubThe code will create a Macro that will paste the texts from the clipboard in cell B4.
After inserting the code,
➤ Click on the Run icon or press F5.
Now,
➤ Close or minimize the VBA window.
You will see the texts from the clipboard have been pasted in cell B4.
Read More: How to Copy and Paste in Excel without Formulas (7 Easy Tricks)
Similar Readings
- How to Copy Unique Values to Another Worksheet in Excel (5 Methods)
- Copy Merged and Filtered Cells in Excel (4 Methods)
- How to Copy Rows in Excel (4 Easy Ways)
- Copy and Paste Thousands of Rows in Excel (3 Ways)‌
- How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
2. Paste from Clipboard by SendKeys
This method is based on the shortcut key CTRL+V which is used to paste data. With a VBA code, you can use this command to paste data from the clipboard. First,
➤ Right-click on the sheet name from the Project panel of the VBA window.
A dropdown menu will appear.
➤ Expand Insert by clicking and then select Module.
It will open the Module(Code) window.
➤ Insert the following code in the Module(Code) window,
Sub Paste_from_Clipboard_2()
ActiveSheet.Range("B4").Select
SendKeys "^v"
End SubThe code will create a Macro named Paste_from_Clipboard_2 that will give the command CTRL+V after selecting cell B4 and paste the data from the clipboard in this cell.
Now,
➤ Close or minimize the VBA window.
➤ Press ALT+F8
It will open the Macro window.
➤ Select Paste_from_Clipboard_2 in the Macro name box and click on Run.
As a result, you will see the texts from the clipboard have been pasted in cell B4.
Read More: Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)
3. Paste from Clipboard in a Range
In this method, I’ll show you how you can copy a range of data from a sheet into the clipboard and paste that data into another sheet from the clipboard.
Suppose, you have the following dataset in a sheet named Data.
Now,
➤ Press ALT+11 to open the VBA window.
➤ Right-click on the sheet name from the Project panel of the VBA window.
A dropdown menu will appear.
➤ Expand Insert by clicking and then select Module.
It will open the Module(Code) window.
➤ Insert the following code in the Module(Code) window,
Sub Copy_Clipboard_Range()
Worksheets("Data").Range("B4:E9").Copy
ActiveSheet.Paste Destination:=Worksheets("Paste sheet").Range("B5:E10")
End Sub
The code will copy the data from B4:E9 of the sheet named Data into the clipboard. After that, it will paste the data in B5:E10 of the sheet named Paste Sheet from the clipboard.
After that,
➤ Close or minimize the VBA window.
➤ Press ALT+F8
It will open the Macro window.
➤ Select Copy_Clipboard_Range in the Macro name box and click on Run.
As a result, your data will be pasted to the desired destination.
➤ Go to the Home tab and click on the little downward arrow icon from the bottom left corner of the Clipboard ribbon.
It will open the clipboard on the left side of your Excel file.
Now, you can see the data which have been pasted in the sheet is in the clipboard. Actually, the data was saved here at first, and then it was pasted to the sheet from here.
Read More: Formula to Copy and Paste Values in Excel (5 Examples)
Conclusion
In this article, you will find 3 methods to paste from clipboard to Excel using VBA. The first two methods will paste the contents of the clipboard in a single cell but with the third method, you can paste data in a range. If you have any confusion please feel free to leave a comment.
Related Articles
- How to Clear the Clipboard in Excel (with Quick Steps)
- Excel VBA: Copy Cell Value and Paste to Another Cell
- How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- Excel VBA Copy Cell Value to Clipboard (4 Methods)
- How To Merge Two Columns In Excel (6 Easy Ways)
- [Solved:] There Is a Problem with Clipboard in Excel
اhi.is there some way to keep the module running all the time and making it save the new value when its is changed?
Hello, HMD!
Thank you for your query. As far as I have understood your query, you want to change the copied values later and thus save it. Now, it is simple. You can do this normally. And, the values will be changed and saved automatically. But, to make this more effective and dynamic, you might need to have a confirmation window for this. To accomplish this, follow the steps below.
Steps:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim KeyCells As Range' The variable KeyCells contains the cells that will' cause an alert when they are changed.Set KeyCells = Range("A1:C10")If Not Application.Intersect(KeyCells, Range(Target.Address)) _Is Nothing Then' Display a message when one of the designated cells has been' changed.' Place your code here.MsgBox "Cell " & Target.Address & " has changed."End IfEnd SubThis code is taken from https://learn.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change
Now, if you insert any new value or remove any value from the cells A1:C10, there will be a Microsoft Excel window informing you about a change in the values. You can change this range inside the code as per your requirements.
I hope this accomplishes your desired result.
Regards,
Tanjim Reza