title: Merging excel spreadsheets into one spreadsheet
body: <p>Okay, I tried to look for similar questions but I didn't understand much of what was being discussed since it's the first time I'm looking at Excel's VBA editor.</p>
  
  <p>In simple terms, I have 2 spreadsheets: "Sheet1" and "Sheet2"</p>
  
  <p>Sheet 1:</p>
  
  <pre><code>    A         B
  1 Header1   Header2
  2 Text1     Info1
  3 Text2     Info2
  </code></pre>
  
  <p>Sheet 2:</p>
  
  <pre><code>    A         B
  1 Header1   Header2
  2 Text3     Info3
  3 Text4     Info4
  </code></pre>
  
  <p>And I would like to have a macro to merge the two sheets into a new sheet (Sheet3), like this:</p>
  
  <pre><code>    A         B
  1 Header1   Header2
  2 Text1     Info1
  3 Text2     Info2
  4 Text3     Info3
  5 Text4     Info4
  </code></pre>
  
  <p>I have tried recording a macro and saving it for later use. To do this, I created a new sheet, copy/paste everything from Sheet1 to Sheet3, then copy all the information except the headings from Sheet2 to Sheet3.</p>
  
  <p>Well, the macro works for this data, but I found that the code generated by excel makes it so it selects the cell A4 (here) before pasting the data. While this works for this data, it wouldn't work if the number of records in each sheet changes now and again. Basically,</p>
  
  <p>1) I was wondering if there was a function that goes to the last relevant cell automatically before pasting the next set of data (in this example, cell A4, and if I have one more table, then cell A6).</p>
  
  <p>2) I've seen the function "ActiveCell.SpecialCells(xlLastCell).Select" (activated when I use Ctrl+End) but that carries me to the end of the sheet. I would need something similar to "Home" and "Down" arrow key after using that function for it to work best.</p>
  
  <p>Either one of those options would be good with me. ^_^</p>
  
  <p>Here's my current VBA code recorded from the Macro Recorder in excel 2010: </p>
  
  <pre><code>Sub Collate_Sheets()
  
      Sheets.Add After:=Sheets(Sheets.Count)
      Sheets(Sheets.Count).Select
      Sheets(Sheets.Count).Name = "Sheet3"
      Sheets("Sheet1").Select
      Range("A1").Select
      Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
      Selection.Copy
      Sheets("Sheet3").Select
      ActiveSheet.Paste
      ActiveCell.SpecialCells(xlLastCell).Select
      ' I need to select one cell below, and the cell in column A at this point
      Sheets("Sheet2").Select
      Range("A2").Select
      Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
      Application.CutCopyMode = False
      Selection.Copy
      Sheets("Sheet3").Select
      ActiveSheet.Paste
  End Sub
  </code></pre>
  
  <p>I hope I didn't forget any useful piece of information. Let me know if I did!</p>