Extract certain columns only

[deduwa]

Hi everyone.

I have a scenario where I get a dynamic (rows & columns) table with headings. All headings in the original data only appear once.

If for example there are 26 columns (A to Z), I only want 5 of these columns (Say columns with headings Apple,Bike,Cat,Drop,Elephant) from the 26 and create in a new sheet a separate table with only these 5 columns with their respective headings & data.

Note that these 5 headings can appear anywhere and not necessarily always in the same position, i.e. 'column number' with each new dynamic table coming through.

Thanks for any help.

 

[deduwa]

Additionally, in the new table, I want the columns to appear in the same order (Apple,Bike,Cat,Drop,Elephant) each time.

 

[alansidman]

Option Explicit


Sub fiveColumns()
    Dim lr As Long, lc As Long, i As Long
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lc = s1.Cells(1, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For i = 1 To lc
        If s1.Cells(1, i) = "apple" Then
            s1.Cells(1, i).EntireColumn.Copy s2.Range("A1")
        ElseIf s1.Cells(1, i) = "bike" Then
            s1.Cells(1, i).EntireColumn.Copy s2.Range("B1")
        ElseIf s1.Cells(1, i) = "cat" Then
            s1.Cells(1, i).EntireColumn.Copy s2.Range("C1")
        ElseIf s1.Cells(1, i) = "drop" Then
            s1.Cells(1, i).EntireColumn.Copy s2.Range("D1")
        ElseIf s1.Cells(1, i) = "elephant" Then
            s1.Cells(1, i).EntireColumn.Copy s2.Range("E1")
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"


End Sub

 

[deduwa]

Thank you Alan