Copy the whole column if top cell equals certain text

[joey1984]

Hi all,

I have two tabs in excel. One called Defects, the second called RawData.

When i extract the data from a system, and copy it to the Raw Data, I want my first tab (Defects) search for where in the Raw Data the first row contains a specific text e.g. Issue Number then copy the corresponding values in that column over to the first tab Defects.

Any ideas?

 

[joey1984]

Hi all,

I have two tabs in excel. One called Defects, the second called RawData.

When i extract the data from a system, and copy it to the Raw Data, I want my first tab (Defects) search for where in the Raw Data the first row contains a specific text e.g. Issue Number then copy the corresponding values in that column over to the first tab Defects.

Any ideas?

Further to the above, I want it error proof, where if in the Raw data the column changes from say Column A to Column B, it still copies the right column.

 

[alansidman]

Option Explicit


Sub joey()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Raw Data")
    Set s2 = Sheets("Defects")
    Dim lc As Long, lr As Long, i As Long
    Dim str As String, lc2 As Long
    lc = s2.Cells(1, Columns.Count).End(xlToLeft).Column
    str = InputBox("What string to search in first row?")
    For i = 1 To lc
        If s2.Cells(1, i) = str Then
            lr = s2.Cells(Rows.Count, i).End(xlUp).Row
            lc2 = s1.Cells(1, Columns.Count).End(xlToLeft).Column
            Range(Cells(1, i), Cells(lr, i)).Copy s1.Cells(1, lc2 + 1)
           
        End If
    Next i
End Sub

This is untested code as there is no worksheet provided to test.