VBA to combine data from Column A in two sheets into Column A in third sheet

[lilvictorian]

Hello,

Looking for help and I think this will be a simple one for the forum. I have a file with multiple sheets. Sheet1 and Sheet2 include a list of IDs in Column A (among other columns which are not the same in the sheets). I'd like to consolidate only the list IDs from Sheet1 and Sheet2 (Column A in both sheets) into Sheet5 Column A so that the IDs are stacked with no spaces. All the sheets include a header row. The number of rows in each sheet will vary.

Thank you for your help!

Example:

Sheet1 Column A
Header
1
2
3
4

Sheet2 Column A
Header
8
9
10

Sheet5 Column A
Header
1
2
3
4
8
9
10

 

[Michael M]

Try using

Option Explicit

Sub MM1()
With Sheets("Sheet1")
.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet5").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
With Sheets("Sheet2")
.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet5").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
End Sub

 

[alansidman]

How to Combine Tables with Power Query - Excel Campus

Use Power Query to combine similar tables together and append new ones. Easily change or delete the query as tables change

www.excelcampus.com

 

[lilvictorian]

@Michael M - thanks for your quick response! I'm not sure if I'm doing something wrong (a distinct possibility), but I couldn't get that to work, as the output didn't include all of the IDs from Sheet1 and Sheet2.

@alansidman - I appreciate the tip on Power Queries. I've never used them but will check it out.

 

[lilvictorian]

I should clarify that the columns in Sheet1 and Sheet2 are not identical, but they both do contain the same heading in Column A (the IDs). Column A is the only column I wish to combine into Sheet5.

 

[Michael M]

Sorry, missed a couple of periods...

Sub MM1()
With Sheets("Sheet1")
.Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet5").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
With Sheets("Sheet2")
.Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet5").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
End Sub

 

[alansidman]

FYI: this does not make a difference in PQ

I should clarify that the columns in Sheet1 and Sheet2 are not identical, but they both do contain the same heading in Column A (the IDs). Column A is the only column I wish to combine into Sheet5.

 

[lilvictorian]

Sorry, missed a couple of periods...

Sub MM1()
With Sheets("Sheet1")
.Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet5").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
With Sheets("Sheet2")
.Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet5").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
End With
End Sub

This works perfectly. Thanks again for a solution and a speedy response. Much appreciated!

 

[lilvictorian]

FYI: this does not make a difference in PQ

Good to know. Sounds like a powerful tool that will come in handy for many applications. I will test it out. Thank you!

 

[Michael M]

Glad to help...