Appending values from different sheets

[beklein]

Hi all,

I feel like I have a question that should not be too difficult to solve, but I just can't figure it out.

I have multiple sheets with identical layouts, I wish to append this values in one total sheets. No sums or anything has to be done, just clear and simple append the values in different cells underneath eachother.

Lets for example say I have one sheet with values in column A:
Bob
John
Sarah

and another sheet with values in column A:
Lisa
Henry
Mary

I want the total tab to show me:
Bob
John
Sarah
Lisa
Henry
Mary

Obviously my real sheet is a bit more extensive, but if I can get the values appended I think I can figure it out.

Anyone has an idea how to do something like this?

 

[mumps]

This macro assumes you have a sheet named "Total" and that your data starts in row 2.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Total" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("A2:A" & LastRow).Copy Sheets("Total").Cells(Sheets("Total").Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[beklein]

Thanks! Didnt expect to need a macro for this. Gonna figure out if there's another setup possible else I'll work with this, thanks for your help!

 

[sandy666]

vba is an option only
also you can try PowerQuery

[SIZE=1]let
    Source = Table.Combine({shtA, shtB, shtC}),
    merge = Table.CombineColumns(Source,{"Sht1", "sht2", "sht3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"sht")
in
    merge[/SIZE]

Sht1		sht
Bob		Bob
John		John
Sarah		Sarah
		Lisa
sht2		Henry
Lisa		Mary
Henry		Bob
Mary		John
		Sarah
sht3		Lisa
Bob		Henry
John		Mary
Sarah
Lisa
Henry
Mary

 

[beklein]

Didn't think of that as well, thanks! Guess I assumed this shouldn't be too complicated using regular Excel expressions, but I wa wrong Gonna look into this as well, thanks

 

[sandy666]

you are welcome

have a nice day