Excel VBA Copy Cell Value to Clipboard: 4 Methods

Method 1 – Copying a Specific Worksheet’s Cell Value to Clipboard in Excel VBA

Step 1: Type the following macro in the inserted Module.

Sub Copy_SWS_CellValue_Clipboard()
Worksheets("5 Aug").Range("E14").Copy
End Sub

Method 1

Macro Explanation

  • VBA Range property takes the specific worksheet and cell value references, and after that the .Copy method copies it to the clipboard.

Step 2: Inserting the macro into the module, use the F5 key or go to Run > Run Macro F5 to execute it.

Run the Macro

 


Method 2 – Excel VBA to Copy Cell Value from Active Sheet to Clipboard

Step 1: Type the below macro in the module.

Sub Copy_AWS_CellValue_Clipboard()
ActiveSheet.Range("E14").Copy
End Sub

Method 2

Macro Explanation

  • VBA ActiveSheet.Range().Copy offers users to provide any cell copy to clipboard after its execution.

Step 2: Hit F5 or repeat Step 2 of Method 1 to execute the macro.


Method 3 – Enabling Auto Copy Cell Values to Clipboard Cell in Excel VBA

Step 1: Paste the following macro into a Sheet’s Code window (Double Click on any Sheet to display the sheet code window).

Private Sub Worksheet_SelectionChange(ByVal cTarget As Range)
If Not Intersect(cTarget, Range("B4:E14")) Is Nothing Then
cTarget.Copy
End If
End Sub

Enabling Auto Copy Cell Values to Clipboard Cell in Excel VBA

Macro Explanation

  • VBA Intersect method returns a range upon the assigned Arg’s intersection.
  • VBA IF function implements a condition and performs .Copy after its affirmation.

Step 2: Return to the specific worksheet; Excel automatically executes the macro.


Method 4 – Using Microsoft Forms Object Library to Send Cell Value to Clipboard

Users can use the Microsoft Forms Object Library to enable “Data Objects” in macros. They need to add the Microsoft Forms 2.0 Object Library from References first.

Activating Microsoft Forms 2.0 Object Library

To activate the Microsoft Forms Object Library, follow the below steps.

Move to Tools > Reference.

Reference-Excel VBA Copy Cell Value to Clipboard

Find the Microsoft Forms 2.0 Object Library within the Available References. After being unable to find it, click on Browse.

Clicking Browse takes you to Windows > System32. Then select FM20.DLL and click Open.

Browse

Excel activates the Microsoft Forms 2.0 Object Library, as shown in the latter image. Click OK.

Activated MSForms DataObject Library

Step 1: Type the below macro in the module.

Sub Send_CellValue_Clipboard()
Dim CellValue As MSForms.DataObject
Set CellValue = New MSForms.DataObject
CellValue.SetText ActiveSheet.Range("E14")
CellValue.PutInClipboard
End Sub

Method 4

Macro Explanation

  • The CellValue variable is assigned as DataObject and gets set to a new MSForms.DataObject.
  • Range() VBA Worksheets.Range provides a specific cell value.
  • .PutInClipboard sends the cell value to the clipboard.

Step 2: Run the macro.


Verification of the Macros’ Outputs

We constitute all the macro variants by assuming different situations. After running or executing (pressing F5) a macro, users may want or need to confirm or paste the copied cell value into another cell.

We need to paste the Daily Sales (i.e., 5 Aug) into another Excel Worksheet (i.e., Sales Report) or anywhere.

Verification

Execute any of the macros, then return to the active worksheet or the 5 Aug worksheet. You’ll be seeing Excel copies the E14 cell or current selection (in Method 3).

You can see the value getting saved in the Clipboard (Home > Click Clipboard Icon).

VBA Macro Copy Cell Value to Clipboard

Paste (CTRL+V) the value in the Sales Report worksheet as desired. The value gets pasted. To say that those macros really copy the assigned cell value to the clipboard as they are supposed to.

VBA Copy Cell Value to Clipboard in Excel


⧭ Things to Keep in Mind

The offered macros only copy the cell values. Cell containing formula results in #REF error in the case of Methods 1, 2 and 3. So, make sure the cell only contains values. Otherwise, users need to paste the values as Paste as Value & Number Formatting.


Download Excel Workbook


Related Articles

 

 

 

 

 

 

 

 

 

 

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo