Determining last copied range

  • Thread starter Thread starter R Avery
  • Start date Start date
R

R Avery

I have the following problem - I often have to copy a range, then paste in a
bunch of places, and then go back to the copied range and make changes. And
since it is often in different worksheets and a lot of scrolling is
involved, it is a very time-consuming process that i would like to automate.

Is there anyway, programmatically, to determine the Address of the copied
range? Or, alternatively, is there some OnCopy event that I can trap and
use to determine the copied range? Ideally, I would like to have a button
or shortcut key that would always bring me back to the site of last copy.

I already have an implementation that would work, if only I was able to
track the event... Is this possible?

Any help would be appreciated.
 
Can't think of any event you can use, and they would all be subject to other
actions. So I think you need to build the whole process

Something like this. Select your cells, run the CopySelection macro, go and
do the pasting, then run the Selection Return macro. Both could be assigned
to Toolbar buttons

Dim rngSelect As Range

Sub CopySelection()
Selection.Copy
Set rngSelect = Selection
End Sub

Sub SelectionReturn
rngSelect.Select
End Sub

Put this all in the same standard code module. Ensure that rngSelect is
declared outside of the macros.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That is a very good idea. I can replace the Ctrl-c shortcut with the new
copy macro... and i still have ctrl-insert for regular copy. its great!
 
Back
Top