title: How to copy a formula horizontally within a table using Excel VBA?
body: <p>So I have data that looks like this </p>
  
  <p><a href="https://i.stack.imgur.com/Rui5l.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Rui5l.png" alt="tables in excel"></a></p>
  
  <p>The top two tables are averaging together certain data from the third table.</p>
  
  <p>I'm trying to write a macro to add an extra column to each table, and simultaneously copy over the formula from the previous column. The adding of a new column is going great, but the copying-over is causing me problems. </p>
  
  <p>This is what I have so far:</p>
  
  <pre><code> Sub What()
  
  Dim m As Integer
  Dim n As Integer
  Dim TableName As String
  Dim i As Integer
  Dim x As Integer
  Dim LastRow As Integer
  
  For i = 1 to 12
  TableName = "Table" &amp; i
  
      With ActiveSheet.ListObjects(TableName)
  
          .ListColumns.Add
  
      n = .ListColumns.Count
      m = n - 1
  
               'this is filling in the first row, with the column number (needed for formulas in the real data)
          .DataBodyRange(1, n).Value = n
          .DataBodyRange(1, n).NumberFormat = "General"
  
      LastRow = .ListRows.Count
  
               'this is where I'm trying to copy the formula over to the right
          For x = 2 To LastRow
              .DataBodyRange(x, n) = .DataBodyRange(x, m).Formula
          Next x
  
      End With
  
    Next i 
  
  End Sub
  </code></pre>
  
  <p>The problem with this is that it's copying the formula straight, so my new column F of Table A1 is still averaging together the values of column E from Table A3.</p>
  
  <p>Before the code I have now, I tried AutoFill (like this: </p>
  
  <pre><code>Range("K4").Select
          Selection.AutoFill Destination:=Range("K4:L4"), Type:=xlFillDefault
  </code></pre>
  
  <p>) </p>
  
  <p>but I don't need it to refer to a range, I need it to refer to a table cell, a la <a href="https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables" rel="nofollow noreferrer">ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2)</a>. Naively I attempted to sub this in for the destination</p>
  
  <pre><code>ActiveSheet.ListObjects("Table1").ListColumns.Add
          ActiveSheet.ListObjects("Table1").DataBodyRange(1, 2).Select
          Selection.AutoFill Destination:=ActiveSheet.ListObjects("Table1").DataBodyRange(1, 3), Type:=xlFillDefault
  </code></pre>
  
  <p>and was told </p>
  
  <blockquote>
    <p>Run-time error '1004':  AutoFill method of Range class failed</p>
  </blockquote>
  
  <p>All the resources I've googled refer to filling a formula down a column, but due to the orientation of my data I need it to copy horizontally, not vertically, and I haven't found anything for that. Can someone please help me with this?</p>
  
  <p>Thanks so much~ </p>