How to Paste From Clipboard to Excel Using VBA

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

➤ Press ALT+F11 to open the VBA window.

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

➤ 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

 

➤ Click on the Run icon or press F5.

vba paste from clipboard to excel

 

➤ Close or minimize the VBA window.

The texts from the clipboard will be pasted in cell B4.

vba paste from clipboard to excel

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell


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

➤ Right-click on the sheet name from the Project panel of the VBA window.

A dropdown menu will appear.

➤ Click Insert and select Module.

module

 

➤ 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

 

➤ 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

The texts from the clipboard will be pasted in cell B4.

vba paste from clipboard to excel

Read More: Excel VBA to Copy Only Values to Destination 


Method 3 – Paste from Clipboard in a Range

We can copy a range of data from a sheet into the clipboard and paste that data into another sheet from the clipboard.

Suppose, we have the following sample dataset in a sheet named Data.

dataset

 

➤ Press ALT+F11 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

 

➤ 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 and it will paste the data in B5:E10 of the sheet named Paste Sheet from the clipboard.

vba paste from clipboard to excel

 

➤ 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

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

You can see the data which have been pasted in the sheet is in the clipboard.

vba paste from clipboard to excel

Read More: How to Use VBA to Paste Values Only with No Formatting in Excel


Download Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. ا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:

      • Go to your desired sheet first where you want to accomplish this.
      • Following, right click on your sheet name and choose the option View Code from the context menu.
      • View Code Option

      • Afterward, insert the following code in the code window and save the Excel file as .xlsm file.
      • VBA Code
        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 If
        End Sub

        This 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.
      Microsoft Excel Window

      I hope this accomplishes your desired result.

      Regards,
      Tanjim Reza

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo