VBA - spontaneous 'Excel cant insert new cells' error

Question

I have been working on a very large macro.

I put the project down for 2 days to work on other things.

I picked it back up, and now I get a 1004 runtime error:

Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet. These 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 try again.

The error presents here in the code:

    Sub Initial_Setup()

    'Create Transform Tab

        Sheets.Add Before:=Worksheets("Lists")
        ActiveSheet.Name = "Transform"

    'Copy Raw Data Over

        Sheets("Sitedata").Select
        Cells.Select
        Selection.Copy
        Sheets("Transform").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    Columns("A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Ive researched this and causes range from hidden characters to autofills to freeze panes any more.

Here is the kicker, this isn't the only place im now getting this error. It doesn't matter the method of moving the data. Paste Special, VBA treats all pasted cells as non-empty cells. Range("A1").Value = Range("A1").Value, VBA treats all pasted cells as non-empty cells.

If I do a =Len() test, its 0 characters. If I do a =IF(a1="","x","") test its "x". If I do =CountA(), I get a 1. Ctrl+end takes me to AA3086, the end of the actual dataset and not by far the furthest column to the right.

The workaround is to try to change "Cells.Select" to a more targeted reference, but that will be the start of a ton of patching in different places.

Has anyone else experienced something like this, or have they been experiencing it in the past couple days?

Thanks,

It would help if you could share a workbook which replicates the problem (just needs the code above and some dummy data) — Tim Williams
Thanks for this. I got home from work, plopped the workbook onto my personal computer, and kicked off the macro, and it still works like a charm. So that means something happened in my companies network 2 days ago that gimped VBA. Your link gives me a first lead to try to trace down what is causing the problem. — Veebeah

Sam Sam · Accepted Answer · 2017-06-21T21:21:49

I think the error message means exactly what it says. As long as your macro inserts rows, it'll trigger that message. If you try it on a brand new sheet, it won't give you any problems, will it?

VBA - spontaneous 'Excel cant insert new cells' error

1 Answers