title: Add New Row and Copy Data To That Row
body: <p>I am looking for help creating a new row in VBA.  Columns A:C are general items, Columns D:F are VBA formulas driven values in Columns A:C. (Basically If Then statements) </p>
  
  <p>Our system, for analysis, requires a single line item for each criteria met.  Row 1 meets two criteria; "Inq" &amp; "High". So I need to insert a new row below, copy the data from row 1 A:C, and in column D enter "High".  That way there is a single row of data for "Inq" and "High".  </p>
  
  <p>The process would be repeated for every row, excluding the newly added ones. Sorry, this may be a little tricky, but I will help out anyway I can.  I am new to Stackoverflow so I couldn't post an image of my table. </p>
  
  <p>---- Below is an update ----</p>
  
  <p>The code below worked great for Column 19. It inserted the row, inserted the values in the new row, and place "Lead" in the last column.   </p>
  
  <pre><code>Sub AddRow()
  
  Dim RowIndex As Long
  Dim Delta As Long
  
  RowIndex = 2
  
  Do While Sheets("WeeklyReport").Cells(RowIndex, 1).Value &lt;&gt; ""
  Delta = 0
          If Sheets("WeeklyReport").Cells(RowIndex, 19).Value = "Lead" Then
          ' Inserts new row
          Sheets("WeeklyReport").Cells(RowIndex + Delta + 1, 1).EntireRow.Insert
          ' Takes cells value from row above and enters value in new row
          Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 1), Cells(RowIndex + 1, 17)).Value = Sheets("WeeklyReport").Range(Cells(RowIndex, 1), Cells(RowIndex, 17)).Value
          ' Puts rating value in last column
          Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 18), Cells(RowIndex + 1, 18)).Value = "Lead"
          Delta = Delta + 1
          End If
      RowIndex = RowIndex + Delta + 1
  Loop
  End Sub
  </code></pre>
  
  <p>Since I have multiple potential values in the RowIndex, I assumed I could just copy the first If Statement, modify it for the next column and everything will work (see code below).  When I ran it, it inserted two rows, only one row copied down, the other blank.</p>
  
  <p>The problem seems to be if there are multiple values per RowIndex.  I will have the potential for multiple values per RowIndex, in which I would like to create a separate row for each.  See example below the code. </p>
  
  <p>Here is my code that I have been working with
      Sub AddRow()</p>
  
  <pre><code>Dim RowIndex As Long
  Dim Delta As Long
  
  RowIndex = 2
  
  Do While Sheets("WeeklyReport").Cells(RowIndex, 1).Value &lt;&gt; ""
  Delta = 0
          If Sheets("WeeklyReport").Cells(RowIndex, 19).Value = "Lead" Then
          ' Inserts new row
          Sheets("WeeklyReport").Cells(RowIndex + Delta + 1, 1).EntireRow.Insert
          ' Takes cells value from row above and enters value in new row
          Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 1), Cells(RowIndex + 1, 17)).Value = Sheets("WeeklyReport").Range(Cells(RowIndex, 1), Cells(RowIndex, 17)).Value
          ' Puts rating value in last column
          Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 18), Cells(RowIndex + 1, 18)).Value = "Lead"
          Delta = Delta + 1
      End If
          If Sheets("WeeklyReport").Cells(RowIndex, 20).Value = "HP" Then
          ' Inserts new row
          Sheets("WeeklyReport").Cells(RowIndex + Delta + 1, 1).EntireRow.Insert
          ' Takes cells value from row above and enters value in new row
          Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 1), Cells(RowIndex + 1, 17)).Value = Sheets("WeeklyReport").Range(Cells(RowIndex, 1), Cells(RowIndex, 17)).Value
          ' Puts rating value in last column
          Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 18), Cells(RowIndex + 1, 18)).Value = "HP"
          Delta = Delta + 1
       End If
      RowIndex = RowIndex + Delta + 1
  Loop
  End Sub
  </code></pre>
  
  <p>Example Values - Below is not code, and not used in the marcro, example only</p>
  
  <pre><code>Example: (RowIndex) A1-A17   Column 19 = "Lead", Column 20 = "HP", Column 21 = "QL"
  Output:  (RowIndex) A1-A17   Column 18 = "Lead"
           (RowIndex) A1-A17   Column 18 = "HP"
           (RowIndex) A1-A17   Column 18 = "QL"
  </code></pre>