Assign copied range to a variable

[johnmasvou]

Hi,
I would like to assign a copied range to a variable.
Note: Sth like Set rngCopied=Selection will not always work because my
selected range and copied range might be different.
Thx

 

[Gary''s Student]

Just dim some range variables and assign them:

Sub demo()
Dim CopiedRangeSource As Range
Dim CopiedRangeDestination As Range
Set CopiedRangeSource = Range("A1:A100")
Set CopiedRangeDestination = Range("Z100")
CopiedRangeSource.Copy CopiedRangeDestination
End Sub

 

[JLGWhiz]

I am not sure I follow your logic. As you write your code, you should know
what you intend to copy, so it is easy enough to set it to an object variable
at the time you determine you want to copy. If you have a variable range,
then use the last row and/or last column techniques to get the current size
of the range. Or you can use the resize method in some cases. There are
several options.

 

[johnmasvou]

Thanks for the reply

Actually, it is not very convenient to copy paste somewhere in the workbook.
The issue is that I have a new workbook event procedure which ultimately
clears the cliboard every time I open a new workbook. Hence, what I would
like to have is sth like:
In my event procedure it should check whether application.cutcopymode=True
and in which case it should assign the copied range to a variable e.g.
rngCopied. Then, after the event procedure has finished, it should re-copy
the rngCopied range.
Is that possible?

Thx