title: VBA - spontaneous 'Excel cant insert new cells' error
body: <p>I have been working on a very large macro.</p>
  
  <p>I put the project down for 2 days to work on other things.</p>
  
  <p>I picked it back up, and now I get a 1004 runtime error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>The error presents here in the code:</p>
  
  <pre><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
  </code></pre>
  
  <p>Ive researched this and causes range from hidden characters to autofills to freeze panes any more.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Has anyone else experienced something like this, or have they been experiencing it in the past couple days?</p>
  
  <p>Thanks,</p>