How can I combine all of the rows from two Excel worksheets into a third worksheet?

Question

I have two worksheets, containing a list of transactions on two separate accounts (a savings account and a credit card). I want to get each row from the first two worksheets into a third so that I can build a pivot table from the consolidated data. How can I achieve that?

That is, I have 10 rows in sheet A, and 10 rows in sheet B, I want to end up with all 20 rows in another sheet C. It's not important to me what order they are in. Both source worksheets have the same format.

I've tried a few things:

• Excel's "Consolidate" feature doesn't seem to achieve this (although it's possible I'm missing something).
• Pivot table features for referencing multiple ranges have the ability for "join"-like logic, but that's not what I want either.
• Pivot table features for multiple consolidation ranges sound good, but I can't get that feature to do what I need either.

I'm using Excel 2013.

There is a significant number of very similar questions, but I haven't found a question that wants to do exactly this.

My data looks exactly like this:

Sheet A

Date        Value       Description
21/01/2016  -$43.11     Pay credit card
18/01/2016  -$180.00    Movie tickets

Sheet B

Date        Value       Description
17/01/2016  -$21.00     Video games
18/01/2016  -$18.00     Beer

Sheet C (desired output)

Date        Value       Description
21/01/2016  -$43.11     Pay credit card
18/01/2016  -$180.00    Movie tickets
17/01/2016  -$21.00     Video games
18/01/2016  -$18.00     Beer

Answer 1

Please try the below sample code..

Sub test()
'change the sheet name and range name as yours

Set Rng = Sheets("Sheet1").Range("A1:A10")
Set Rng1 = Sheets("Sheet2").Range("A1:A10")

i = 1
j = 0

For Each cell In Rng
Sheets("Sheet3").Range("A" & i).Value = cell.Value
Sheets("Sheet3").Range("A" & i).Offset(0, 1).Value = cell.Offset(0, 1).Value
Sheets("Sheet3").Range("A" & i).Offset(0, 2).Value = cell.Offset(0, 2).Value
i = i + 1
Next cell
j = i

For Each cel In Rng1
Sheets("Sheet3").Range("A" & j).Value = cel.Value
Sheets("Sheet3").Range("A" & j).Offset(0, 1).Value = cel.Offset(0, 1).Value
Sheets("Sheet3").Range("A" & j).Offset(0, 2).Value = cel.Offset(0, 2).Value

j = j + 1
Next cel

End Sub

Hope this will help you