Referring to a range that has been copied (Excel-VBA)

[Aalamo]

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

 

[Ryan H]

I'm not sure if there is a way to access the range properties from the
clipboard. This code will display the range address of the range you are
copying or cutting. Hope this helps! If so, let me know, click "YES" below.

Put this code in the Worksheets SelectionChange Event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' get previously selected range address
strOldRange = strNewRange

' get currently selected address
strNewRange = Target.Address

' show old range address if it is being copied or cut
If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy
Then
MsgBox strOldRange
End If

End Sub

Put this code in a standard module:

Public strNewRange As String
Public strOldRange As String

 

[Aalamo]

I'm not sure if there is a way to access the range properties from the
clipboard.  This code will display the range address of the range you are
copying or cutting.  Hope this helps!  If so, let me know, click "YES" below.

Put this code in the Worksheets SelectionChange Event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' get previously selected range address
    strOldRange = strNewRange

    ' get currently selected address
    strNewRange = Target.Address

    ' show old range address if it is being copied or cut
    If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy
Then
        MsgBox strOldRange
    End If

End Sub

Put this code in a standard module:

Public strNewRange As String
Public strOldRange As String

--
Cheers,
Ryan











- Show quoted text -

Ryan, many thanks for the time and the idea. Unfortunately it is not
what I require. In your code a user can select a range, then copy, and
then select another range. At this stage your code tells you what the
address of the one in copy is, i.e. the first selected range's
address. So far so good. The problem is that when you select a third
range you lost the reference to the original one being copied and
instead you change provide the address of the second one your
selected, i.e. you only remember the previously selected range, not
the one that has been Copied - which is what I need.

Basically, I need to retrieve the address of the range that will be
copied, at any one time, whatever range is selected.

Thank you

Leo