title: Excel VBA: Insert row into table and fill down everything from row above
body: <p>I have a problem with a little bit of VBA code I pulled together and I just can't find an answer somewhere else.
  I have a table named TableOPQuery which has over 40 columns and over 10k rows.</p>
  
  <p>There is a column called SPLITS where the user will write a value x (integer). If that value is greater than 1 then a row will be inserted under the row where the user wrote the value, because the purpose of that value is to add rows and copy everything the original row had (values, formulas, format) to make the same amount of rows specificied by the user including the original one, so it woul be like "x - 1".</p>
  
  <p>Here is an example, because I propably couldn't explain it good enough:</p>
  
  <pre><code>Order   Provider       Amount   Type    Splits  Shipped
  23     Shady company    10000   Whole   1   
  30     That company     2000    Split   2   
  *30     That company     2000   Split*
  35     This company     420     Whole       
  </code></pre>
  
  <p>So, you see, in row 1 (order 23), the user wrote 1, so no rows will be inserted. But, in  row 2 (order 30), the user wrote 2. So one more row will be inserted, copying everything from the row above (the one where the user inserted 2), to make 2 rows identical to each other.</p>
  
  <p>I managed to piece toger this code that helps me in inserting whatever amount of rows the user wants, but for some reason I can not make it fill down from the original row where the user wrote the value and I want it to clear the contents in the SPLIT row to not trigger the code again.</p>
  
  <p>I am stumped now, because the normal filldown fuction doesn't work. I can insert rows, <strong>but I cant copy and fill down everything the row above has</strong>, and I can't clear the column SPLITS either.</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As range)
      Dim KeyCells As range
      Dim xValue As Integer
      Dim tbl As ListObject
      Dim tRows As Long
      Dim tCols As Long
      Dim originCell As String
  
      'I call a fuction that will give me the position of the column that has SPLITS in it, searching a predefined row (5:5). I know this is unnecessary but this is the best I could do because the column SPLITS might change of position (add/delete columns)
      col = ColumnNumberByHeader("Splits")
  
     'I use this to get the amount of rows the table has mostly
      Set tbl = ActiveSheet.ListObjects("TableOPQuery")
      With tbl.DataBodyRange
          tRows = .Rows.Count
          tCols = .Columns.Count
      End With
  
     'An If to get a range using the a predefined start row (5), the col I got earlier, and the amount of rows the table has. If I get 0 as col is because the column does not exist
      If col &lt;&gt; 0 Then
          Set KeyCells = range(Cells(5, col), Cells(tRows, col))
      Else
          Cancel = True
          MsgBox "Check that column SPLITS exist"
          Exit Sub
      End If
  
      'Here is where the level noob magic happens. Rows start getting inserted if a value in the range I got in KeyCells happens
      If Not Application.Intersect(KeyCells, range(Target.Address)) Is Nothing Then
          'If the value is not numeric then nothing will run
          If IsNumeric(Target) Then
              'If the target is greater than 1 then the amount of Target.Value minus 1 of rows will be inserted under the row where the change occurred
              If Target.Value &gt; 1 Then 
                  originCell = Target.Address       
                  xValue = Target.Value - 1
                  MsgBox "Cell " &amp; Target.Address &amp; " has changed."
                  'A loop to insert the rows, I use - 4 because the Target.Address is of the whole worksheet, and not the table itself.
                  For i = 1 To Target.Value - 1 Step 1
                      tbl.ListRows.Add (range(Target.Address).row - 4)
                      'Filling down into the inserted rows from the row of the originCell (where the user inserted the value)
                      range(originCell).EntireRow.FillDown
                  Next i
              End If
          End If
      End If
  End Sub
  </code></pre>