VBA / Macro Copy & Paste errors

[xlquestions]

Hi!

First off, a little bit of background. I have a spreadsheet workbook which I use for business. It consists of 13 sheets, some with very little data (being used as a day planner), and a couple with a few hundred rows (customer data etc). The entire workbook is currently 575KB. It's an XLSX, Office 2007. I've used this spreadsheet for around 3 years, on a couple of different computers running Windows 7. The various sheets use data validation and conditional formatting to some extent - nothing major.

Over the years, I found that the workbook became increasingly slow and unstable - copying more than one row, in particular, often caused the spreadsheet to hang, "Whitescreen" crash and restart Excel. I tried many solutions such as cutting out unnecessary conditional formatting, removing excess sheets, splitting it into several different workbooks with different sheets in each file etc and nothing ever seemed to work.

A few days ago, prompted by some very annoying repeated crashing (hard to avoid copying lots of rows in, say, the daily planner, every time the week changes!), I dissected the spreadsheet and, after a few hours of digging (including exporting the whole thing as .csv and starting to create a new workbook from scratch), found the culprit - every time I had copied a cell, or set of cells, Excel had been copying (duplicating) the conditional formatting with it, so when I went into the conditional formatting menu I found tens - possibly hundreds - of thousands of duplicate conditional formatting rules. As Excel has no easy way to mass-delete lots of conditional formatting rules (but leave some), I cleared off ALL the conditional formatting and wrote new conditional formatting (hereafter shortened to CF for brevity) rules from scratch.

Bingo! The workbook was back up and running and full speed like new, and I was even able to add back in a few CF rules that I had a long time back but deleted when the spreadsheet got unstable a year or so ago.

Now obviously, I didn't want the same situation to repeat itself so I started researching on the web for a way to "lock" the cells so that cell contents could be copied or cut WITHOUT the CF rules or data validation (Hereafter DV) moving along with it. I found the "paste special" > "Values" option to just copy the text (or double click the cell (or F2) and copy the text, then do the same to the destination cell to paste only text).However, the latter has the issue that you can only paste one cell's worth of info that way, not multiple rows/columns and the former has the issue that, well, it's not very user-friendly. Sure, I can add a "Paste special" icon to the ribbon or quickbar or whatever, but it's just too ingrained in myself and other users to use Ctrl+C or Ctrl+X and Ctrl+V.

I did look into "Lock Cells" but apparently that doesn't work (insofar as keeping the CF and DV grounded in that cell whilst moving data). I found several forum threads and other posts around the web of people asking how to do similar things and having similar problems to my own, and the consensus always seemed to be that it's impossible to lock CF and DV to cells in this way, protected from copy/cut and paste.

After a bit of hunting around though, I did stumble across the following piece of VBA code, posted HERE:

Sub PasteasValue()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

As instructed, I pasted this code in as a VB script module, created a macro for it and bound the macro to Ctrl+V (to replace Windows/Excel's normal Paste option) and it worked fine! I've copied a few cells around within the same sheets that have CF, and no more "copies" of the CF rules are appearing in the CF menu. All seems fine, problem solved....

Except... Now I get errors. Every time I try to copy text from any other program into Excel, any time I try to copy cells in other, completely different, completely unrelated workbooks (whilst the main one is open) within their own workbook, whenever I try to cut rather than copy cells within the main workbook itself (even within the same sheet), I get errors.

For example, I just tried to cut a cell from one location on the same sheet within the main workbook to a different cell location (same workbook, same sheet). Error:
"Microsoft Visual Basic
Run-Time error '1004':
PasteSpecial method of Range class failed
"

Now, I try copying text (a single word) from notepad into an cell on a sheet of the problem workbook:
Same error as above.

Then I open a completely separate spreadsheet workbook (no ties or links to the main one, completely standalone) and try to paste the line of text from Notepad in (with the main sheet still open):
Same error.

Now trying to cut a cell from within that (different) spreadsheet / workbook to another cell on the same sheet:
Same again.


So it seems that this VB script is somehow 'hooking' into Windows' global Ctrl+V, or at least Excel / Office's own weird internal copy/paste clipboard.

---------


So, what I'm looking for is a modification to the above code that will perform the same job (replace Ctrl+V with a script which will always paste as value, allowing me to copy/paste cells in the normal fashion within the main workbook WITHOUT copying the CF / DV behind the scenes stuff). BUT, I'd prefer:

* No hooks outside this spreadsheet, so when this file is open and other excel spreadsheets are open, I can Ctrl+C/Z/V normally in those without the issue of my 'main' workbook catching that
* No errors! Able to copy/cut and paste data from other sources (such as notepad, firefox and maybe other spreadsheets) without errors. Also to be able to cut cells within the same workbook (the main one)

If the above isn't possible, maybe there's another way to 'lock' CF and DV into the sheet so that copy (or cut) and paste won't replicate those entries in the main list (CF), or move (for example) lookup lists from one column to another (with DV). Or, maybe some other way that I can't think of that will make these error messages just go away ^_^.

I've included some screenshots of the error and relevant areas of the spreadsheet. I've only included one screenshot of the error because it's the exact same error whether I'm copying into the main workbook, cutting cells within that workbook, cutting cells (or copying in) to a different workbook while the main is open, etc.

Sorry for the long message and thanks in advance for any assistance with the code! I hope I've covered everything but if you have any questions, please let me know.

 

[BrianM45]

Hi xlquestions

The Paste special as value work in the same instance of Excel
He dont work with a copy from another application, it is not made for that

to avoid errors, you can try this code

Sub PasteasValue()  On Error Resume Next
  Selection.PasteSpecial Paste:=xlPasteValues
  On Error GoTo 0
End Sub

 

[xlquestions]

Thanks for the reply . I tested that code and it certainly stops the errors... However the paste operation still doesn't happen (i.e. the text from notepad won't get pasted into the main or secondary workbooks, and cutting cells still won't work etc).
I do get why - the code is bypassing the error message, however it'd be great if there was some way of making it work? Or simply locking the CF and DV in place so cut/copy/paste won't move or duplicate them

Thanks for your reply though - it's nice not to have the error message. Now just need to get it working

 

[xlquestions]

I just wanted to update this thread with some info that I've discovered since posting. I posted the same thread on another Excel forum and got a response that it would be a good idea to share the excel file itself in case of problems with the actual file - my response and subsequent thoughts on the issues at hand are included below.

Sorry if this is an inappropriate thread bump/necro - I won't bump it again, just wanted to share my discoveries so far. In particular, you can check out the section at the bottom called "Update to main issue" - essentially I've figured out that the issue with Paste Special / Paste Values seems to be that it doesn't like pasting values from outside Excel, or cutting cells within the sheet, hence the error. So what I really need is a way to solve the initial problem with copying or cutting cells without also duplicating / moving / removing the CF or DV settings - if that's even possible. At this rate, I don't think that Paste Values will solve my problem due to issues that Excel has with using "paste values" on external data or cut cells from within the same sheet. If anybody has a solution to this problem I'd really, really appreciate it!

-------

It can be difficult to help you without having the actual sheet to work with. Often times a description of a problem is inaccurate. I'm not saying yours is, but that can be a reason that no one has tried to help because although you offer a lot of detail it's easier for an expert to just troubleshoot the actual sheet than help you based on your description. A mechanic wants to hear your complaint about your car, but he also wants the car in his possession to see for himself.
Same thing here.

There are many threads here where people go back and forth many many times because they don't want to post a sheet. Try this, didn't work, try that didn't work, it can be a bit cumbersome doing it that way.

You should post your actual sheet with sensitive data removed.

Having said that, have you even tried changing the shortcut key to something that Excel doesn't use for something else to see if the issue is still there?

I've used control Q in the past for some of my macros.

I do totally understand what you're saying - in another life I work in IT and I know the frustrations of intermittent problems or trying to diagnose the issue remotely, however I'm very concerned for the security and confidentiality of the data and anything that might be "lurking beneath the surface" in even a sanitised sheet.

I had a little think though and, luckily, it's easy for me to upload a problem sheet - because the issue is inherent to Excel itself and not the sheet itself, I simply opened a new, blank workbook, deleted Sheet2 and Sheet3, popped the bit of code into a module and added the Ctrl+V macro - and the exact same problems still occur - i.e. the error messages when trying to copy into the file, or cut between cells within the same file.

I've attached the .XLSM file for your reference.

(I can't attach files as far as I can see here on MrExcel, but this file can be easily replicated: Open a new blank workbook, delete sheets 2 and 3, Open the VBA editor (Alt+F11), create a new module and paste in the code below, save the file as a .xlsm, close the VBA editor, open up Macros (Alt+F8), go to Options and set the shortcut key to Ctrl+V, then save the file and you're done!)

It's a simple, blank workbook with just one sheet and only one piece of data - just nonsense text "jdahfgsaf" in cell A1. There are no conditional formatting rules, no data validation - the only thing in the file is the code:

Sub PasteasValue()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

in a module, and a macro set up with a hotkey of Ctrl+V to run that script.

If you open the file, try the following:

1. Try to cut the cell A1 (or any other cell), to another cell anywhere on the same sheet
2. Try to copy or cut text from, say, Notepad or Firefox into that spreadsheet (any cell - not F2'ing into the cell first, just pasting normally)
3. Open this sheet, then open any other spreadsheet on your computer and try to do (1) or (2) in that other sheet - not even interacting at all with this one.

(I've just noticed that if you open a "new" copy of excel, the script does not carry across and the Ctrl+V works fine, BUT that's because it's a totally new copy of excel and so the script isn't under the 'wing' of the main copy, if that makes sense. Not sure how to explain. But in order to see the problem for step (3), drag your working sheet into the new Book1.xlsm one, or create a new blank workbook from within Book1, in order to ensure that it's opening within the same instance, not another one).

The steps above will cause the error to occur. You can also text this on your own copy - just paste the code above into a VBA module and link it via macro to Ctrl+V - should work on any spreadsheet (or not work, as the case may be!)

Regarding setting a different hotkey, that was my initial thought when setting this up for the first time, however I was concerned that myself and other users would forget to use the new shortcut key - force of habit to use Ctrl+V - and so I'd end up with the same problem that I had before with copying the CF etc.
However I did just test this with the Book1 sheet attached - I changed the hotkey to Ctrl+Q. In this case, Ctrl+V pastes 'normally' (i.e. without paste values, circumventing the whole solution that I was trying to do - however the errors do not occur (as expected, since the code is not executing)), and now Ctrl+Q causes the same error message as shown above.
This does seem to be an issue with the code itself, or an intrinsic Excel problem rather than anything to do with the sheet data or hotkey.



Quick edit:

It occurs to me, after doing a bit of searching around that the main reason for OTHER spreadsheets, which are open at the same time as the one with the VBA code, exhibiting the same error is simply that they are open in the same instance of Excel - since opening a different saved Excel spreadsheet file when you already have one open causes the second one to open in the same program instance, looking at alt+F11 (VB mode) you can see that all of the different workbooks are shown along with right hand side, so I assume that the same code is executing for any spreadsheet open within that instance. That also explains an issue I had where I was trying to replicate this issue and found that I could cut/paste and copy in data fine with a new sheet - but that was because I opened the new sheet from the start menu, actually opening a new instance of the Excel Exe, and therefore the code from (in this case) Book1.xlsm wasn't applying to that instance.

Tl;Dr The problem described above is caused by:
A) Trying to cut and paste cells anywhere within the affected spreadsheet, and
B) Trying to copy (or cut) and paste data from another program (such as notepad) into the affected Excel spreadsheet.
The issue with OTHER unrelated spreadsheets exhibiting the same errors when open at the same time as Book1 is due to an Excel oddity of opening multiple separate spreadsheet files within the same program .exe instance and applying VBS code to all spreadsheet instances within that program instance. It's NOT part of the 'faulty' code, as such.

-------

As an unrelated tangent, I've been trying to fix the issue of Excel opening multiple files within one copy of Excel - Under Excel Orb > Excel Options > Advanced > General you can tick "Ignore other applications that use Dynamic Data Exchange (DDE)", which sort-of works except that:
A) Closing all spreadsheets, with this tick box "on" and then trying to open my spreadsheet from my usual shortcut opens an instance of Excel but gives the error "There was a problem sending the command to the program", and then sheet refuses to open. Once that instance of Excel is open, I can then go to recent files and open the sheet fine.
B) Even once the main sheet is open (and at that point I can open other spreadsheets fine, with DDE turned off), all future sheets opened open within their own shared instance of excel, so if I opened a different spreadsheet FIRST, and then opened the main sheet, and then any more sheets, any opened after "Main" (the second sheet, and first in its' instance) would all share the same instance and thus the code, rendering this solution unhelpful at best.

I'm currently researching other ways of fixing this, including some registry hacks that might solve the issue, and will report back if I discover anything. However this issue is tangential to the main problem that I'm facing so everything after Quick edit in this post can mostly be ignored, it's just for interest and another slight annoyance (it's an Excel issue but responsible for my main code problem "spreading" to other open sheets).





Update to main issue!!!

Just figured out something interesting.

When doing "Cut" on a cell (in any spreadsheet), if you then go to right click / content menu, one can see that "Paste Special" is greyed out.
When copying or cutting from Notepad, and going to context menu > Paste special in Excel, you CAN do this, HOWEVER - only "Unicode Text" and "Text" options are available. The paste special menu is totally different from the one that appears when you "Copy" a cell and then paste special, which shows (amongst others), Paste Values.

My guess is that the code is failing because, for some reason, you can only "Paste Special" in Excel when copying from a cell within that workbook. Trying to cut a cell, or trying to cut/copy from elsewhere does not allow paste special > paste as values. This narrows my question quite a bit:


A) Is there a way to force Excel to allow "Paste Special > Paste values" for data brought in from outside Excel, or when cutting cells within Excel?
B) If (A) is impossible, is there a way to paste the text of a cell ONLY (ignoring the cell's conditional formatting and/or data validation rules?
C) If (B) is impossible, is there a way to "lock" conditional formatting and/or data validation rules so that they will NOT be duplicated (in the case of CF) or moved / replaced / deleted (in the case of DV) when a cell is copied or cut from one place to another? (This was really my original intention all along - the "Paste special" code was simply a stop-gap solution that kind-of-worked, so I've been following that path more than others, but really a solution to that original issue is what I am looking for)