Run-time error 1004: this action won't work on multiple selections

I have posted this question before but it was for sharepoint. I thought the error was due to sharepoint but now the file has been moved to a local folder yet it still happens sporadically. I am starting it as a new question hoping it will get answers.


The routine is to copy data from some child workbooks and paste to the parent file. The error happened on the line pastespecial. The error happens once in a while so I am wondering if anyone has had similar experience as I am not sure if it's the routine itself or an excel bug? Any help appreciated!!

Please Login or Register  to view this content.

It is difficult to analyze code when it is not posted. That message usually indicates that you are trying to copy and paste data from non adjacent fields, or on offset rows and columns to an area of adjacent cells. Something that VBA and Excel are not set up to handle as a single action. If you need more information, please post the applicable code so we can view it.

Thanks! I was just trying to clean up the code and you were quick

I noticed that the wording for the error is a bit different than "this command can not be used on multiple selections". It must be the difference between copy pasting non adjacent cells or some others like merged cells?

I tried to select a cell after copying and clear the clipboard to undo the selecting/copying but not sure if it fixes all.

The code ran fine on my computer however it threw an error yesterday on another computer. I just tried a moment ago and it seems fine on my computer. I will know if it happens again today on another computer.

Unless you specify copy to clipboard, vba generally will just copy to a memory cache other than clipboard, so whatever is on the clipboard should not be causing the error message. The merged cells could be the problem if you are not getting the message on your computer, but others are. You can fix that with a Cells.Unmerge statement at the beginning of the code. But that could upset some of the users when they lose their pretty formats.

The code you posted will not run on my computer, but it looks to me like you are copying contigent cells in a range and except for something like merged cells, it should not give the message you are getting.

I have checked and pretty sure there's no merged cells. There may be wrapped text but don't think it matters. I had a commented cell and it was dramatically slowing down the copying (googled and shout out to Contexture.com) but that was dealt with.

The funny thing is it doesn't happen all the time. It happens sporadically and that's why I wonder if anyone had similar experience or it's an Excel bug. I will know if it happens again and will update if you are interested.

No hidden columns no text to columns at all

Maybe this will work better in that copy-paste section:

Please Login or Register  to view this content.

1004 errors are catchall errors that are usually generated by something the user does that could not be anticipated by the programmers of the VBA application. It can range from typos to inaccurate references. Usually when the error occurs sporadically, it is related to a part of the code that only executes under certain conditions. I don't see the latter condition in the posted code, but obviously the entire code is not posted and we have no idea what your data looks like. The questions being asked above are all aimed at trying to determine where the code could be looking and what it is finding when it looks. If you click the Debug button when the error occurs and then check all of your sheet, range and variable references to make sure they are what they should be, you could likely isolate the problem.

Thanks @ xladept I may try the code to see if the error goes away permanently.

@JLGWhiz: The error happens on the line selection.pastespecial

You're welcome and thanks for the rep!

We've seen Excel act up when Selection and Select are used. I hope it solves your problem

Originally Posted by aprildu

@JLGWhiz: The error happens on the line selection.pastespecial

Understood, but the cause of the error does not have to be on that line of code. There is nothing wrong with that code syntax, but it has a problem executing the line because of a condition elsewhere and none of the code posted indicates where that might be. I was just attempting to say how you might be able to find where the problem is. But perhaps @xladept has the right idea to rewrite the code to get rid of the select and selection syntax. That way, the code shows where the data is coming from and going to more clearly.

@LGWhiz: I think I might have used the select to de-copy (madeup word). I thought the error might be because there was a copying somewhere else already or the whole sheet of the child file was selected before it was opened (therefore multiple selections). I will try the non select method to see if it makes a difference.

I have not heard from the user of the error again yesterday (code not changed yet). finger crossed.