Excel 2016 - vba Insert Column code problems

Question

Hi,

I have a macro that inserts a column into a spreadsheet using:

Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

This code works fine in Excel 2007 and Excel 2010 but it has stopped working correctly in Excel 2016 (I don't have access to Excel 2013 so not sure on this one). I believe the reason maybe because further up the code it has copied and pasted a selection of data. I believe that the insert column code above is now inserting copied cells instead of inserting a whole column (even though I have already pasted that data).

I have noticed that there have been changes to the way copying works in the latest update to Excel 2016 (Version: 1705) and just wondered if this could be the cause of my new problem as I'm pretty sure it wasn't doing this before.

I believe I can fix the problem going forward by using application.cutcopymode = false in the right place but it would be good to know if this has only just started happening with the new update and that it is restricted to Excel 2016 only.

Thanks,

Sarah

Answer 1

Sorry Joanne, I meant to reply with more detailed information but I haven't had the time as yet.

I do know the solution to the problem, I just wanted to check if it was caused by the new June update in Excel 2016 (so I know how long my spreadsheets have been working incorrectly).

The insert column code does indeed work correctly in the 1705 version until you copy and paste a selection of data before you use the code, then it does not insert a new column to the left, it inserts copied data from your last selection. To stop it doing this, I have ensured that Application.cutcopymode = false is added to my code directly after the paste.

I have now fixed my spreadsheets and am pretty sure that this behaviour changed with the June update so I don't need further help.

Many thanks,

Sarah

Answer 2

With the move from Excel 2010 to Excel 2016, I have been struggling with the same issue using Selection.Insert Shift:=xlDown to insert rows.  I get the error message that "Microsoft Excel can't insert new cells because it would push non empty cells off the end of the worksheet".  This occurs even when I delete rows prior to the Selection.Insert statement.

Using the Application.CutCopyMode = False statement before the Selection.Insert statement has solved my problem.

Thanks for providing the solution.

Steve

Answer 3

Hi,

I would like to thank Sarah for creating this thread and also providing the solution on how to overcome the bug. 

With the new update of office on July 7th 2017 script is not working as expected. I never had this problems earlier. I am using the same script in Excel 2010, 2013 & even 2016 but in Version: 1705 its not working as expected. when ever it tires to execute 'Selection.Insert Shift:=xlDown' it pasting the data which is copied further up in the code. I had to use 'application.cutcopymode = false' Just above every xlDown statement. 

Thanks,

Dinesh

Answer 4

I can verify this exact issue is happening to me with the last office update.  And the cutcopymode = false before every insert is the only thing that's fixing it.

I sincerely hope Microsoft fixes this in their next update patch because I have thousands of lines of codes in hundreds of workbooks that will need to be fixed because of this.

Answer 5

Sarah,

Thanks for taking the time to share your experience.

I was scratching my head on this one and found your post at just the right time - an hour after you had posted it.

I made the fix using application.cutcopymode = false prior to issuing the paste command and that resolved the issue.  I'd like to think Microsoft will fix it, but realize that is unlikely to happen.

Best,

Kevin