Search for specific column header names, copy columns and paste to append to another wookbooksheet

Question

My workbook has one,two or three sheets. Each sheet can contain at least one of these column header names: "Tel" or "Number".

How can I copy the entire columns ( data only) with these column header names and paste them (as an append in just one column with the same column header name) into another workbook sheet where the VBA code ( Sheet Module) is. Thanks.

Answer 1

Option Compare Text

Sub search_and_append()

    Dim i As Long
    Dim width As Long
    Dim ws As Worksheet
    Dim telList As Object
    Dim count As Long
    Dim numList As Object
    Set telList = CreateObject("Scripting.Dictionary")
    Set numList = CreateObject("Scripting.Dictionary")


    ' search for all tel/number list on other sheets
    ' Assuming header means Row 1
    For Each ws In Worksheets
        If ws.Name <> Me.Name Then
            With ws
                .Activate
                width = .Cells(1, .Columns.count).End(xlToLeft).Column
                For i = 1 To width
                    If Trim(.Cells(1, i).Value) = "Tel" Then
                        Height = .Cells(.Rows.count, i).End(xlUp).Row
                        If Height > 1 Then
                            For j = 2 To Height
                                If Not telList.exists(.Cells(j, i).Value) Then
                                    telList.Add .Cells(j, i).Value, ""
                                End If
                            Next j
                        End If
                    End If
                    If Trim(.Cells(1, i).Value) = "Number" Then
                        Height = .Cells(.Rows.count, i).End(xlUp).Row
                        If Height > 1 Then
                            For j = 2 To Height
                                If Not numList.exists(.Cells(j, i).Value) Then
                                    numList.Add .Cells(j, i).Value, ""
                                End If
                            Next j
                        End If
                    End If
                Next
            End With
        End If

    Next

    ' paste the tel/number list found back to this sheet
    With Me
        .Activate
        width = .Cells(1, .Columns.count).End(xlToLeft).Column
        For i = 1 To width
            If Trim(.Cells(1, i).Value) = "Tel" Then
                Height = .Cells(.Rows.count, i).End(xlUp).Row
                count = 0
                For Each tel In telList
                    count = count + 1
                    .Cells(Height + count, i).Value = tel
                Next
            End If
            If Trim(.Cells(1, i).Value) = "Number" Then
                Height = .Cells(.Rows.count, i).End(xlUp).Row
                count = 0
                For Each tel In telList
                    count = count + 1
                    .Cells(Height + count, i).Value = tel
                Next
            End If
        Next
    End With

End Sub