I have asked this question at a couple of other places and nobody has been able to come up with a solution. Before asking this question I already believed there wasn’t a solution to this, but I will try it again to see if someone should happen to have a solution for this.
In Excel when you copy a cell the border starts moving/flashing. Only when that border is moving you are allowed to paste its contents into another cell. Once the border is no longer moving, you are also unable to paste its contents. The other option is to double click on the cell or go into the formula bar to manually copy the data, which then you can paste as much as you want, at least until something else is copied.
Here is my problem. I have VB script within the “Private Sub Worksheet_SelectionChange(ByVal Target As Range)” section of my worksheet. So every time another cell is selected this script is run. The script works perfectly and I do not need this changed. The spreadsheet first needs to be unlocked before the script can run, so I had to put this at the top of the script “ActiveSheet.Unprotect” and this at the bottom “ActiveSheet.Protect”. When I select a different cell and the code hits either the unprotect or the protect commands, then the copied cell border is no longer flashing, so I can’t paste.
Is there a script of some sort that I can use so it retains the copied data? The script should only run when a cell is copied. I also don’t want to use the SendKeys function because that typically causes more issues then what it solves. I have always been wondering where Excel stores its data for copied cells, since usually copied data is stored in the windows clipboard, or some other dumping ground since Windows 7 & 8 no longer use the clipboard for copping data. So I don’t understand why Excel doesn’t use the same method as everything else when a cell is copied.
Other information asked from the other sites:
What is the code that I am using: The code that I am using can be found at the below link. It is the one with the green checkmark as the answer, the one that I wrote. Although, for the most part it is irrelevant to this issue, other than knowing that when it hits the unprotect line, that is when it removes the flashing borders of the copied cell. For this script you see here, the unprotect wouldn’t be needed, but due to the fact that there is a lot of code in other locations of this spreadsheet I had to put the unprotect in there due to the spreadsheet being protected. If I didn’t unprotect it here, then the script would error out because it isn’t able to make changes. http://stackoverflow.com/questions/17005931/excel-2010-vb-script-highlight-row-issue
Clarification on how I am copying cells: Someone thought I had script to copy a cell from point A to point B. No, when I say copy I am just using the typical Ctrl + C on the keyboard. Due to the script activating as soon as another cell is selected, it prevents the copied cell from remaining active so I can never paste.
This isn’t too major of an issue since cells are very rarely copied in this spreadsheet, but just knowing the fact that it doesn’t work is driving me crazy, I like things to fully work the way they are supposed to. I would greatly appreciate it if someone knows of a solution to this.
asked Jun 27 '13 at 17:34
Thanks grooveDexter for the page, it is somewhat helpful. I think what I need is a script that will pull the last copied (top) item from the Excel Clipboard and paste it in the cell when an item is pasted (Regardless if I use Ctrl + V, right click on the cell and select paste, or go to the home menu and select paste) . I can tell the copied cell is retained in the clipboard, even when I select a different cell and the border stops flashing; however I can’t paste that item in the desired cell unless I click on the item in the clipboard. I need to someone write a script that will retain the top item in the clipboard in memory somehow, so when I need it the paste feature will be available. Of course the item will need to be overwritten when something new is copied. I have no idea on how to do this, I even tried recording macros and that doesn’t help.
answered Jul 18 '13 at 11:03
Your problem might be that Office has its own clipboard that is capable of storing up to 24 items: http://office.microsoft.com/en-us/excel-help/copy-and-paste-multiple-items-by-using-the-office-clipboard-HA010163602.aspx
answered Jul 17 '13 at 14:35