I assure you this is a difficult question, so read it carefully before attempting to help.
Please, don't point me to any "rookie programmer links" or "sample clipboard code links". I am already fairly expert at VBA and have spent hours looking at links. I found a few that addressed this exact problem but those experts concluded that it can't be done within the Excel Object Module.
The user selects several cells and does a Ctrl C, then selects another cell anywhere they want and then call my macro, PasteSpecialSum which inserts formula '=sum([book.xls]sheet1!a1:
a5,...)' into the target cell.
PasteSpecialSum retrieves the users range off the clipboard which I discovered was very difficult. I developed the attached ReselectClip code to solve the problem. But it is complicated, so the goal of this questions is to come up with a better way.
I do NOT want the user to call my macro first, so please do not suggest code like
set DesiredRange = inputbox( "Please select desired cells" ....type:=8)
I also do NOT want my user to select the destination first. The ctrl C must be done first.
Also, I can't use events because the user might switch to a cell in another workbook, then call PasteSpecialSum. And they might select various cells before deciding where to paste it. This means I cannot easily use events like Selection_change.
NOTE: Code posted below is not the correct code for this issue. Please see this comment for the correct code: https://www.experts-exchange.com/Q_27251773.html#a36356176