This tutorial solves a problem where Excel won’t allow you to insert new rows or columns in a worksheet. When you try this, Excel displays the message “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet. Those non-empty cells might appear empty but have blank values, some formatting or a formula. Delete enough rows or columns to make room for what you want
to insert and then try again.” The most probable reason you are getting this message is that in the last row or column of your worksheet, there is either formatting, data or a formula. This might not be easy to spot, but by following the instructions below you can easily clear the offending cells. A quick way to navigate to the last column in your worksheet is to: A quick way to navigate to the last row in your worksheet is to: If you can see either data, a formula or formatting in the last row or column, this is probably where the problem
lies. Here is a fairly reliable way to clear all cells to the right of or below your data [including the last column and row]: Once you have completed these steps, try inserting a row or column. You may get a warning message titled ‘Large Operation’. The warning indicates that inserting new cells may take a significant amount of time to complete. You can ignore the warning and click on OK. If the method above does not work, try this method: You will need to repeat these steps for each column or row you need to insert. If
method 1 and 2 fail, Try method 3. Method 3 requires you to reset the ‘used range’ in your worksheet. To do this:METHOD 1: Clear Last Row or Column
METHOD 2: Copy to a New Worksheet
METHOD 3: Clear the ‘Used Range’
03-18-2011, 02:45 PM #1
Why can't I copy and then insert copied cells?
I am trying to copy A225:Q227 and then "insert copied cells" and "shift cells down", but the option to "insert copied cells" is greyed out. A long long time ago I asked how to prevent copying and pasting, and recall the ability to disable that in the advanced settings, but the stopping of copy and paste is not selected there.
Thoughts? This is quite frustrating. :-[
Last edited by jonvanwyk; 03-23-2011 at 09:44 AM.
03-18-2011, 04:10 PM #2
Re: Why can't I copy and then insert copied cells?
Do you have any event code in the worksheet? Does this apply to every worksheet/workbook or just specific ones?
Remember what the dormouse said
Feed your head03-22-2011, 08:49 AM #3
Re: Why can't I copy and then insert copied cells?
I think I might have an event code, but cannot find it if I do. My workbook is too large to attach in the post, but you can download them directly at:
Excel 2003 Sample: //www.jonvanwyk.com/excel2003form.xls
Excel 2007 Sample: //www.jonvanwyk.com/excel2007form.xlsm
03-22-2011, 09:13 AM #4
Re: Why can't I copy and then insert copied cells?
Originally Posted by jonvanwykI am trying to copy A225:Q227 .....but the option to "insert copied cells" is greyed out.
Which sheet are you trying to copy from/paste to?Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
John Wright03-22-2011, 09:18 AM #5
Re: Why can't I copy and then insert copied cells?
Eventually it will be each worksheet #'s 1 through 26, but I am starting with worksheet "1".
03-22-2011, 03:34 PM #6
Re: Why can't I copy and then insert copied cells?
Originally Posted by jwright650Which sheet are you trying to copy from/paste to?
I am trying to copy from A225:Q227 and paste to A229:Q231 within each worksheet "1" through "26".I cannot find any code that would be preventing this from being possible, and an just stumped at this point :-/
03-22-2011, 05:39 PM #7
Re: Why can't I copy and then insert copied cells?
It is due to the fact you have code running in the SelectionChange event, so the clipboard gets cleared when you select the destination before pasting.
03-23-2011, 07:59 AM #8
Re: Why can't I copy and then insert copied cells?
Originally Posted by romperstomperyou have code running in the SelectionChange event, so the clipboard gets cleared when you select the destination before pasting.
Ahhhh....I saw that but didn't realize what happens to the copied info....makes sense.....Thanks for the explanation.Will the OP have to comment out the SelectChange event code to make all of the changes needed, then un-comment the code to put the workbook back in use?
03-23-2011, 08:29 AM #9
Re: Why can't I copy and then insert copied cells?
Depends - if it's only Copying that needs to be allowed, we can lock the clipboard at the start of the routine and then unlock it at the end, and it should be fine. That won't work for Cutting though. [interestingly, they seem to have resolved this in 2010 as it works fine as it is]
03-23-2011, 09:23 AM #10
Re: Why can't I copy and then insert copied cells?
Originally Posted by romperstomperDepends - if it's only Copying that needs to be allowed, we can lock the clipboard at the start of the routine and then unlock it at the end, and it should be fine. That won't work for Cutting though. [interestingly, they seem to have resolved this in 2010 as it works fine as it is]
That explains why it works at home on 2010, but not at work on 2007 :-/ I don't need to cut, just copy and paste at this point...how do we make it so "we can lock the clipboard at the start of the routine..."03-23-2011, 09:28 AM #11
Re: Why can't I copy and then insert copied cells?
That also explains why this is a recent phenomena...I added that "SelectionChange" code not too long ago...I just haven't tried to copy and paste for a while. I thought my project was done, but then realized I need ONE LAST OPTION in my drop down box, and therefore need to clone some forumulas and change the names to protect the innocent :-P
03-23-2011, 09:29 AM #12
Re: Why can't I copy and then insert copied cells?
At the top of a normal module, put these two lines:
Then at the beginning of the selection change code, use:
and then at the end:03-23-2011, 09:40 AM #13
Re: Why can't I copy and then insert copied cells?
Like I have done below? Also, Do I need to change "user32" to something else?
03-23-2011, 09:43 AM #14
Re: Why can't I copy and then insert copied cells?
Nevermind...I tried it as I asked above, and it worked....so thank you.
Excel is like Life...no matter how much you think you know, there is always more to learn and always someone else who is better than you. :-]
03-23-2011, 09:44 AM #15
Re: Why can't I copy and then insert copied cells?
Correct, and no you don't need to change the declarations.
08-18-2015, 08:20 AM #16
Registered User
Re: Why can't I copy and then insert copied cells?
I am facing the same problem, when any selected rows 'right clicked, the "insert" is greyed out >>.disabled & when we copy the highlighted row, right clicked, then the "Insert Copied Cell " is also greyed out, this is valid and applied in all my workbooks.............Please suggest.
I tried the above in module but does not work