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.
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.
➤ 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.
➤ Click on the Run icon or press F5.
➤ Close or minimize the VBA window.
The texts from the clipboard will be pasted in cell B4.
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.
➤ 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.
➤ 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.
The texts from the clipboard will be pasted in cell B4.
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.
➤ 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.
➤ 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 and it will paste the data in B5:E10 of the sheet named Paste Sheet from the clipboard.
➤ 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.
The 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.
You can see the data which have been pasted in the sheet is in the clipboard.
Read More: How to Use VBA to Paste Values Only with No Formatting in Excel
Download Practice Workbook
Related Articles
- How to Copy Visible Cells Only without Header Using VBA
- How to Use VBA PasteSpecial for Formulas and Formats in Excel
- How to Use VBA PasteSpecial to Keep Source Formatting in Excel
- Excel VBA: Copy Row If the Cell Value Matches
- Copy and Paste Values to Next Empty Row with Excel VBA
- How to Autofilter and Copy Visible Rows with Excel VBA
- VBA Paste Special to Copy Values and Formats 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