title: Use a button in Excel to copy information from one sheet to another on a new row
body: <p>I have one workbook with two sheets. Sheet 1 is laid out to look like a form with a submit button and named <strong>TravelRequest</strong>. Sheet 2 is just a database that is collected from sheet 1 and named <strong>TravelLog</strong>.</p>
  
  <p>Here is how it works now:</p>
  
  <ol>
  <li>User on Sheet 1 fills out the proper sections of the Excel form  </li>
  <li>User clicks on the Submit button</li>
  <li>Data gets copied onto Sheet 2 in its own columns all in 1 row and clears Sheet 1 entries</li>
  <li>When the next user fills out the form it should add a new ROW in Sheet 2</li>
  </ol>
  
  <p>So, right now my script copies one cell to another specified cell and I tried many different codes from this website but cant seem to get any to work, also my copy script is hardcoded copy &amp; paste operations. I don't know how to work around that. </p>
  
  <p>I can upload the Excel sheet somewhere if anyone needs it for helping out here.</p>
  
  <pre><code>Sub Submit()
      Application.ScreenUpdating = False
      Range("L5").Copy
      Sheets("TravelLog").Range("B6").PasteSpecial xlPasteValues
      Range("C5").Copy
      Sheets("TravelLog").Range("C6").PasteSpecial xlPasteValues
      Range("G5").Copy
      Sheets("TravelLog").Range("D6").PasteSpecial xlPasteValues
      Range("c10").Copy
      Sheets("TravelLog").Range("E6").PasteSpecial xlPasteValues
      Range("c9").Copy
      Sheets("TravelLog").Range("F6").PasteSpecial xlPasteValues
      Range("I9").Copy
      Sheets("TravelLog").Range("G6").PasteSpecial xlPasteValues
      Range("I10").Copy
      Sheets("TravelLog").Range("H6").PasteSpecial xlPasteValues
      Range("C13").Copy
      Sheets("TravelLog").Range("I6").PasteSpecial xlPasteValues
      Range("C14").Copy
      Sheets("TravelLog").Range("J6").PasteSpecial xlPasteValues
      Range("C15").Copy
      Sheets("TravelLog").Range("K6").PasteSpecial xlPasteValues
      Range("C16").Copy
      Sheets("TravelLog").Range("L6").PasteSpecial xlPasteValues
      Range("C17").Copy
      Sheets("TravelLog").Range("M6").PasteSpecial xlPasteValues
      Range("C18").Copy
      Sheets("TravelLog").Range("N6").PasteSpecial xlPasteValues
      Range("i13").Copy
      Sheets("TravelLog").Range("O6").PasteSpecial xlPasteValues
      Range("i14").Copy
      Sheets("TravelLog").Range("P6").PasteSpecial xlPasteValues
      Range("i15").Copy
      Sheets("TravelLog").Range("Q6").PasteSpecial xlPasteValues
      Range("i16").Copy
      Sheets("TravelLog").Range("R6").PasteSpecial xlPasteValues
      Range("i17").Copy
      Sheets("TravelLog").Range("S6").PasteSpecial xlPasteValues
      Range("h20").Copy
      Sheets("TravelLog").Range("W6").PasteSpecial xlPasteValues
  
      Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p><br /></p>
  
  <hr />
  
  <p><strong>* EDIT *</strong></p>
  
  <p>With druciferre's answer, I'm getting this error</p>
  
  <blockquote>
    <p>ERROR OVERFLOW </p>
  </blockquote>
  
  <p>on this line</p>
  
  <pre><code>Worksheets("TravelLog").Range(Dest).Value = Worksheets("TravelRequest").Range(Field).Value
  </code></pre>
  
  <p>Here is the updated <code>refTable</code> array.</p>
  
  <pre><code>refTable = Array("B = L5", "C = C5", "D=G5", "E=C10", "F=C9", "G=I9", "H=I10", "I=C13", "J=C14", "K=C15", "L=C16", "M=C17", "N=C18", "O=I13", "P=I14", "Q=I15", "R=I16", "S=I17", "W=H20")
  </code></pre>