How to Paste From Clipboard to Excel Using VBA

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.

clipboard


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.

reference

It will open the Reference- VBAProject window.

➤  Check Microsoft Forms 2.0 Object Library and click on OK.

library

It will activate the Microsoft Forms 2.0 Object Library. Now,

➤ Click on the Insert tab and select Module.

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 Sub

The code will create a Macro that will paste the texts from the clipboard in cell B4.

vba paste from clipboard to excel

After inserting the code,

➤ Click on the Run icon or press F5.

vba paste from clipboard to excel

Now,

➤ Close or minimize the VBA window.

You will see the texts from the clipboard have been pasted in cell B4.

vba paste from clipboard to excel

Read More: How to Copy and Paste in Excel without Formulas (7 Easy Tricks)


Similar Readings


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.

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 Sub

The 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.

vba paste from clipboard to excel

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.

macro box

As a result, you will see the texts from the clipboard have been pasted in cell B4.

vba paste from clipboard to excel

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.

dataset

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.

vba paste from clipboard to excel

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

code

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.

vba paste from clipboard to excel

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.

vba paste from clipboard to excel

As a result, your data will be pasted to the desired destination.

vba paste from clipboard to excel

➤ Go to the Home tab and click on the little downward arrow icon from the bottom left corner of the Clipboard ribbon.

vba paste from clipboard to excel

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.

vba paste from clipboard to excel

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

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo