Pulling values from columns based on column headings

[kimcindyj]

Hello,

In A1, I have Nov2013. Columns A2:Z2 have various headings with data underneath each column. I would like to find the column with the heading "Nov2013" and pull the all the data underneath that column into a new column, column AA2.

How can I do this?

Thank you!

 

[JLGWhiz]

Since you specified that cell A1 contains the same string as the target header, I assume you are using cell A1 as an entry box. If not, then this code fails. This code is written as Worksheet_Change event code, which means that the entry into cell A1 will trigger the macro to run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fLoc As Range
If Target.Address = "$A$1" Then
    Set fLoc = Range("A2:Z2").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not fLoc Is Nothing Then
            Range(fLoc.Offset(1, 0), Cells(Rows.Count, fLoc.Column).End(xlUp)).Copy Range("AA2")
        End If
End If
End Sub