CODEWITHSUNDEEP
vbaexcel
sigil
sigil

Reputation: 9546

How to copy range to clipboard for use in another Excel instance?

I have two different Excel books bk1 and bk2 open, in two different instances so that I can have one open on each monitor.

I want an event handler for bk1 so that when I double-click on a column in sheet s1, I copy all the values in that column to the clipboard, so that then I can click on bk2 and paste these values into a column.

I have the following event handler in bk1, in the code module for s1:

Private Sub worksheet_beforedoubleclick(ByVal target As Range, cancel As Boolean)

Dim column As Long
Dim rng As Range

column = target.column
Set rng = Range(Cells(2, column), Cells(3233, column))
rng.Copy


End Sub

This event fires when I double-click, and it assigns the correct range to rng, but when I try to paste the values, the Paste option isn't available, even if I try to paste the values somewhere else in bk1. Why isn't the range copied to the clipboard?

Note that if I change the last line to:

rng.Copy Sheets("sheet3").Range("a2")

the paste occurs successfully. So the problem is getting the data to the clipboard.

Upvotes: 1

Views: 5578

Answers (2)

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

At First I thought may be Double clicking inside the cell contents Clears the excel Clipboard

Then I open the clipboard in Excel to see if the selected data is being copied to the clipboard or not by clicking the arrow as shown below

enter image description here

Turns out the data is copied to the clipboard but for some reason it wouldn't paste the data

So, I finally was able to get the clip board object with some help 

Private Sub worksheet_beforedoubleclick(ByVal target As Range, cancel As Boolean)
Dim column As Long
column = target.column
Set rng = Range(Cells(2, column), Cells(3233, column))
rng.Copy

    Dim Clip As Object
    Set Clip = CreateObject( _
     "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Clip.GetFromClipboard
    Range("B2").PasteSpecial xlValues       

End Sub

Feel free to modify the Clip object to your use

Upvotes: 1

IAmDranged
IAmDranged

Reputation: 3020

The double-click looks to switch the cutcopymode to false

Upvotes: 1

Related Questions