title: Excel AutoFill problem
body: <p>I'm having problems with a VBA script. The function fills in a sheet with some formulas. It works fine most of the time, however when I run it on a larger data set... it crashes with:</p>
  
  <p>Run-time error '1004': AutoFill method of Range class failed</p>
  
  <p>The debugger points to the last AutoFill call. When I look at the sheet, it seems that the AutoFill call worked about 3/4 of the way but stops.</p>
  
  <pre><code>With Sheets("OPENS")
      ' Fill OPENS formulas
      .Range("B" &amp; firstRowOPENS - 3) = "=DATE(YEAR($B$1), MONTH($B$1)+2, DAY($B$1))"
      .Range("B" &amp; firstRowOPENS - 3).NumberFormat = "[$-409]mmm-yy;@"
      .Range("C" &amp; firstRowOPENS - 3) = "=C1+2"
  
      .Range("B" &amp; firstRowOPENS - 1) = "=SUBTOTAL(109,B" &amp; firstRowOPENS &amp; ":B" &amp; lastRowOPENS &amp; ")"
      .Range("B" &amp; firstRowOPENS - 1).AutoFill .Range("B" &amp; firstRowOPENS - 1 &amp; ":" &amp; lastColOPENS &amp; firstRowOPENS - 1), xlFillValues
  
      .Range("B" &amp; firstRowOPENS) = "=SUM(C" &amp; firstRowOPENS &amp; ":" &amp; lastColOPENS &amp; firstRowOPENS &amp; ")"
      .Range("C" &amp; firstRowOPENS).FormulaArray = "=SUM( ('Combined Input'!R2C2:R" &amp; lastRowCI &amp; "C2=RC1) * ('Combined Input'!R2C45:R" &amp; lastRowCI &amp; "C45=R2C) * ('Combined Input'!R2C43:R" &amp; lastRowCI &amp; "C43 = R1C4) * INDEX('Combined Input'!R2C5:R" &amp; lastRowCI &amp; "C40, 0,R" &amp; firstRowOPENS - 3 &amp; "C3,1)) * R[-" &amp; (lastRowOPENS - firstRowOPENS + 5) * 2 &amp; "]C" &amp; OPENS_PROB_COL
      .Range("C" &amp; firstRowOPENS).AutoFill .Range("C" &amp; firstRowOPENS &amp; ":" &amp; lastColOPENS &amp; firstRowOPENS), xlFillValues
  
      If lastRowOPENS &gt; firstRowOPENS Then
          .Range("B" &amp; firstRowOPENS &amp; ":" &amp; lastColOPENS &amp; firstRowOPENS).AutoFill .Range("B" &amp; firstRowOPENS &amp; ":" &amp; lastColOPENS &amp; lastRowOPENS), xlFillValues
      End If
  
      setBorders .Range("A" &amp; firstRowOPENS - 2 &amp; ":" &amp; lastColOPENS &amp; lastRowOPENS)
  End With
  </code></pre>
  
  <p>Any suggestions?</p>
  
  <p>Edit:</p>
  
  <p>I found the problem: <a href="http://support.microsoft.com/kb/166342" rel="nofollow noreferrer">http://support.microsoft.com/kb/166342</a></p>
  
  <p>"In Excel 2003 and in earlier versions of Excel, a single worksheet may contain a maximum of 65,472 array formulas that refer to another worksheet. If you want to use more formulas, split the data into multiple worksheets so that there are fewer than 65,472 references to a single worksheet. "</p>