vba - Merging excel spreadsheets into one spreadsheet

Question

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.

In simple terms, I have 2 spreadsheets: "Sheet1" and "Sheet2"

Sheet 1:

    A         B
1 Header1   Header2
2 Text1     Info1
3 Text2     Info2

Sheet 2:

    A         B
1 Header1   Header2
2 Text3     Info3
3 Text4     Info4

And I would like to have a macro to merge the two sheets into a new sheet (Sheet3), like this:

    A         B
1 Header1   Header2
2 Text1     Info1
3 Text2     Info2
4 Text3     Info3
5 Text4     Info4

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.

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,

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).

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.

Either one of those options would be good with me. ^_^

Here's my current VBA code recorded from the Macro Recorder in excel 2010:

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

I hope I didn't forget any useful piece of information. Let me know if I did!

Answer 1

Jerry, try this code. I cleaned up your code a bit and made it more efficient to be able to do what you wish. I've made some assumptions based on what your code said which I think are right. If not, comment on this answer and I will tweak if needed.

Option Explicit

Sub Collate_Sheets()


   Sheets.Add After:=Sheets(Sheets.Count)
   Dim wks As Worksheet
   Set wks = Sheets(Sheets.Count)

   wks.Name = "Sheet3"

   With Sheets("Sheet1")

    Dim lastrow As Long
    lastrow = .Range("B" & .Rows.Count).End(xlUp).Row

    .Range("A1:B" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp)

   End With

   With Sheets("Sheet2")

    lastrow = .Range("B" & .Rows.Count).End(xlUp).Row

    .Range("A2:B" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)

   End With


End Sub