Aalamo wrote on 01/04/2010 08:32 ET :
Hi,

Can anyone confirm if there is a way in VBA to refer to a range that
has just been copied but that it is not the selected range?

For example: I select Range A1:A2, I press Ctrl+C and dotted moving
lines appear around A1:A2. Now, I move to any other cell, say B4. At
this stage, A1:A2 remain highlighted with the dotted lines, so I
understand that the Application "knows" of them, therefore how can I
return their address through code? Selection.address will obviously
not work as it is no longer the selected range...I looked within the
Application object but could not find anything...

The fact that Excel knows that the range A1:A2 is the range that will
be copied upon a paste operation, makes me think that the range is
"active" in memory in some way and it could be retrirvable
information, but perhaps it is one of those things the object model
hides from us.. I tried reading the clipboard, but there you only get
the values of those ranges, not the ranges as Range objects, with
address property, formula property, etc...

Any ideas?

Many thanks

Leo

Here's how I trap the copied range in Excel 2000:

Usiing PERSONAL.XLS

In module:

Global grngCopied As Range

In ThisWorkBook:

Private WithEvents App As Application
________________________________________
Private Sub Workbook_Open()