title: Excel 2010 PasteSpecial method of range class failed
body: <p>I am attempting to understand why a certain bit of code will not work in Excel 2010. </p>
  
  <p>I have a file where cells A1:K200 are part of a table (the Format as Table option form the Home tab), but rows 2-13 are hidden for use with data validation combo boxes. Header row and row banding options are enabled. What I want to do is copy (via the user pressing Ctrl-C) and paste (via user pressing Ctrl-V) data from another workbook and then have a macro function in <code>Worksheet_Change</code> that will transpose the data (based on the value of a checkbox on the worksheet) and paste only the values. Columns A-H will be filled out with other data.</p>
  
  <p>I have disabled events and screen updating, set up a range for cells I14:I200 as <code>StdCost</code> for an intersect function and then have the following code to trap changes to the above range. <code>optTranspose</code> is a checkbox I have placed on the sheet and <code>Target</code> is the variable set up by Excel as part of the <code>Worksheet_Change</code> function. </p>
  
  <pre><code>If Not Application.Intersect(Target, StdCost) Is Nothing Then
      If Application.CutCopyMode = xlCopy Then
          Application.Undo
          .Range("I" &amp; Target.Row).PasteSpecial Paste:=xlPasteValues, Transpose:=optTranspose
      End If
  End If
  </code></pre>
  
  <p>The above code will work, but based on my testing it will only work with a maximum of 4 values being pasted in OR it will work if I paste starting on row 15 or below. However, if I paste 5+ values I get the error from the subject</p>
  
  <blockquote>
    <p>PasteSpecial method of range class failed</p>
  </blockquote>
  
  <p>From my testing, what appears to happen is that before the undo operation kicks in, Excel automatically adds <code>Column1</code> and <code>Column2</code> to columns L and M, which then causes <code>Target</code> to take on "Column1" and "Column2" and thus ends up causing the error when I try and run it on the actual values I want to paste. If I convert the table to a normal range, everything appears to operate as expected. </p>
  
  <p>I want to understand why "Column1" and "Column2" are being inserted by Excel, why it's only at 5+ cells from another spreadsheet that it becomes and issue and how I might be able to account for this in my code.</p>