How to get Copied range address in VBA

I am searching a VBA code which will return the copied range(Dashline border /Marching ant rang). Seeking the kind help from excel experts.

Background of the problem:
Let's say I copy a range. In Excel, Copied Range will be highlighted with a dashed border. Then I just select another cell. Now I will run a macro. After running the macro, in messageBox, I want to see the cell address of the Copied range (not the cell address of the selection).

Example:

1. Lets say I Copied Range("$A$1:$A$5") and select B1Cell. When I will run the macro, messageBox will show $A$1:$A$5.
2. Or Lets say I Copied Range("$C$5:$E$10") and select B1Cell. When I run the macro, messageBox will show $C$5:$E$10.

Solution looking for:
1. If only contiguous cell range is copied, then VBA code will return on ONE copied cells address.
2. If multi-range are copied, then there will be 02 result. One is for Union of all individual separate range and another one will return one cell address from left top corner to bottom right corner.

I am not very much an expert in windows API. So I really need your kind help.



Just want to mention what I have found. So far, I have found 03 ways:-

1. Paste as link method: To me, this is not the right method. This is a trick. Also It obviously still has some holes. Good thing that this returns multiple selection copied range. But This is a very slow method when a full column is copied. (Attachment)

2. Worksheet_SelectionChange method: This method is not workable for me. Because my macro will be used in any sheet (Like as excel add-in).

3. Window API (or something else) method: With adding additional/3rd party libraries, I have seen many ways. But I could figure out how to use it.


Get the copied range address.xlsm

I don't think there is a way to get the address of the cells that were copied but you could do it in 2 steps. Here is a file where you select the cells you want to copy then click the 'Copy' button.
Then you select where you want to paste it to and click the paste button. It tells you the address of the cell(s) you are pasting, and the cell you are pasting to. If you haven't picked a different cell to copy to then it will tell you that too.
It still follows all the rules of manually copying and pasting though. If you try to copy a range of cells that is not allowed, it tells you. If you copy non contiguous cells then paste it somewhere, it pastes them as contiguous.
There is 1 module that has a public variable in it.
So with this file, make a selection and click the 'Copy Selection' button. Then select a different cell and click paste. It will tell you the range that was copied and ask if you want to paste it into the cell that is selected.
I hope you can incorporate this into your file.
If you can and you like the way it works, I hope you will add to my reputation.

Dear Sir, This is a WOW.... working amazing. I have converted your file Add-ins and this is working fine with all excel file.

Originally Posted by Andy Pope

You can do it with a combination of VBA and ribbon XML

the ribbon XML provides the command syntax to capture the user clicking the Copy button.

The VBA code makes use of the OnKey method to capture CTRL+SHIFT+c or CTRL+c

Thisworkbook code module

Please Login or Register  to view this content.

Module1 code

Please Login or Register  to view this content.

ribbon xml


The workbook has a new tab with a button on it. Copy a range and then press the button to get details of copied range.

Dear Sir, This is a WOW.... working amazing. I have converted your file Add-ins and this is working fine with all excel file.