1

I have an Excel 2010 workbook that I've been using for a while with no issues. All the sudden, today I can't insert copied cells anymore. I usually:

  1. highlight the rows I need to copy
  2. right click -> Copy
  3. right click where I want to insert the cells -> Insert Copied Cells

Now when I do this, the cells show up for a fraction of a second and disappear (the screen flashing part). Two questions:

  1. How can I fix this?
  2. What causes this to happen?
2

I figured it out. The solution to question 1. is an obscure radio button:

File -> Options -> Advanced > Display options for this workbook: -> For objects, show -> All


EDIT: After a year and a half, I've figured out the answer to question 2. It turns out the obscure radio button is not the only way to change the object showing behavior. Apparently the keyboard shortcut Ctrl+6 turns object showing on and off as well.

The only Excel workbook that I've ever had this problem with was my to-do list. The reason it was happening was that when I went to use the keyboard shortcut to strikethrough the contents of a cell (Ctrl+5) to mark an item as completed, every once in a while I would miss and hit Ctrl+6.

Microsoft's Excel keyboard shortcut page confirms this:

Alternate between hiding objects, displaying objects, and displaying placeholders for objects

CONTROL + 6

What gave it away was tonight I realized the screen flashed when I accidentally hit Ctrl+6. Hopefully this helps others who have Excel to-do lists!

0

Knowing Excel and by that you say you use the workbook for a while now, the source of the issue is possibly that the file is full of garbage (Excel is not really good in cleaning up reverted actions or removing cell-reference if you delete data from it with the DEL key).
First, make a copy of your workbook by using Excel's "Save As" option and saving it under a different name (Excel's "Save As" may clean up the file a bit), then copy and paste this code into your new workbook and run it (follow the run and test rules written on the site!).
You can do the same by hand if you don't want to use macros: select all Columns which are not in use and delete them with right-click "Delete..." on the selection, then do the same with all unused rows. Do this on all and every worksheets.
Check the file size to see if it's significantly smaller now. If it is, you are done. If not, destroy all your graphs & charts and re-create them, one by one (yes, it's painful). After deleted one, save the file and check file size - do the same after re-creation (you may find out one of the charts or graphs or inserted pictures drastically grows up the file's size).

If issue is not sorted out, give more details about what are you trying to copy, from where and to where and what active macros you have as you may face another kind of issue (e.g. a macro blocks the copy as it doesn't work as intended, or the copied content have some issues and by that, cannot be copied "as is"). Also try to copy a different range of cells on a different spreadsheet to see if you get the same result.

  • I agree with Mark. You might also try copying the offending worksheet into a new (clean) workbook and attempting the copy there. If it works, you've verified the original workbook contains the problem. – Robert Kerr May 5 '13 at 16:42
0

Nice quick solution. Its amazing what objects can do to your excel file. As a quick add on solution to above, when I followed the instructions:

File -> Options -> Advanced > Display options for this workbook: -> For objects, show -> All

the embedded objects weren't visually apparent, and I could not find any button or other object just by glancing at my sheet. Also, my workbook contains 10+ sheets and the data tables are many columsn and rows.

So to find if there were any objects in my workbook, I used a similar solution I read somewhere else (with regards to solving the problem with Excel size being very large). You need to search your work book and sheets using the Find & Select button in Excel 2007:

Find & Select -> Go To Special... -> Objects.

This brings you right to the object and highlights it. Hit delete on your keyboard (even if you can see the object). Run again, and on each sheet, until there are no objects found. You will get a pop up that there are no objects.

After this, to be safe, I did a Save As as well after all this.

Size went from 2MB to 650k, and copy and paste errors, along with some other weird activity are no more.

As for the cause, question 2, in my case, the objects appeared because I copied and pasted data from an Internet based software I was using. There must have been some hidden objects that got copied over. A solution to this, always paste copied data as text when copying from an external source. This way, you dont get any garbage along with the data.

  • Interesting approach. I tried this when this error happened again and got the "No objects found." popup so it looks like it's caused by something else in my case. – Patrick Dec 8 '15 at 20:42
0

Another cause of the missing "Insert copied cells" is if there are filters in effect in the area being copied. Clear the filters and all that worked before should work again.

0

Yes if there's a filter and there's data you need to select in the filtered data to then insert somewhere else there is a workaround Copy the data that you have filtered into a new excel paste it there and then Copy again and then the insert copy cell option will start working again

0

Unhide all rows and columns.

I was facing an issue when trying to copy rows, I would get an error saying that the action wasn't supported. The simple fix ended up being un-hiding some columns! Working fine since then!

0

My similar problem was caused because the worksheet was protected. On the ribbon, choose Review, "Unprotect sheet" (or "Unprotect Workbook"). Simple, but not obvious for 20 minutes!

0

I had encountered with this problem once, Just unhide the columns in the worksheet you are about to copy, then insert copied cells option will be shown in the destination worksheet

  • Hi, Wanderer. Welcome to StackExchange. When answering, be sure to check others' answers to make sure you don't repeat an existing answer. It looks like Meera provided the same answer on Nov 2, 18. – Patrick Mar 2 at 7:49
0

Easier way to solve this problem by running a Macro with the code mentioned at bottom of this message.

To open the VBA Editor (Press Alt+F11), then Right click on VBA Project (Top-Left) > Select Insert > Click Module.

Paste the below code and Run Macro (F5). Reopen the excel.

Public Sub Menu_Reset()
 CommandBars("column").Reset
 CommandBars("row").Reset
 CommandBars("Cell").Reset
 End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.