PPMConsultant
asked on
Quickly copy contents of a cell to clipboard
A user is constantly having to copy and paste numbers from a spreadsheet into another windows application, and we are looking to speed this process up. From my understanding there is a way to add some VBA in Excel that will copy a cell's value when double clicking the cell. I'd like something like that, or another similar way like middle clicking or even putting a button with a macro next to each cell in that row. Any help out there?
Hi PPM,
Right-click the tab you want to add this functionality to. Go to View Code. In the codepane that appears for that sheet, paste in the following:
If the user wants to do this to all sheets in a workbook, we can add a workbook-level event to the ThisWorkbook object in VBA. Let me know if interested.
Cheers
Matt
Right-click the tab you want to add this functionality to. Go to View Code. In the codepane that appears for that sheet, paste in the following:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Copy
Cancel = True
End SubIf the user wants to do this to all sheets in a workbook, we can add a workbook-level event to the ThisWorkbook object in VBA. Let me know if interested.
Cheers
Matt
ASKER
What would I do if I want this to only be active for cells in Column A?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I needed. Short and sweet.
Since you probably don't want the copying to happen everywhere on the sheet, let me suggest this modification that only does it in A1 to A10 (which of course can be changed).
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
Target.Copy
End If
End SubASKER
Is there a way to have it do this on every sheet, but only column A?
Put this in the code for Thisworkbook.
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Not Intersect(ActiveCell, Sh.Range("A:A")) Is Nothing Then
Target.Copy
End If
End Sub
Martin is correct, put it in the ThisWorkbook object in vba of the workbook you want it to run (if you can't see the Project window where ThisWorkbook is, press control-r to open it). In order to avoid references the active cell, substitute Target in place of ActiveCell. Also cancel out the double click after copying to cancel out the enter-cell behavior of double clicking:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Sh.Columns("A"), Target) Is Nothing Then
Target.Copy
Cancel = True
End If
End Sub
pls insert in your workbook module
Open in new window
Regards