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?
PPMConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls insert in your workbook module

Private Sub SheetBeforeDoubleClick(Sh, Target, Cancel) 
    Sh.Target.Copy
End Sub

Open in new window

Regards
0
mvidasCommented:
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:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Target.Copy
 Cancel = True
End Sub

Open in new window


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
0
PPMConsultantAuthor Commented:
What would I do if I want this to only be active for cells in Column A?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mvidasCommented:
You can test to see if the clicked cell is in column A using the Intersect method:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Not Intersect(Columns("A"), Target) Is Nothing Then
  Target.Copy
  Cancel = True
 End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PPMConsultantAuthor Commented:
Exactly what I needed. Short and sweet.
0
Martin LissOlder than dirtCommented:
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 Sub

Open in new window

0
PPMConsultantAuthor Commented:
Is there a way to have it do this on every sheet, but only column A?
0
Martin LissOlder than dirtCommented:
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

Open in new window

0
mvidasCommented:
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.