You could use a macro with code similar to the following:

Cells.Find(What:="demand", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
columntouse = ActiveCell.Column
Columns(columntouse).Select
Selection.Copy
Sheets("Sheet2").Select
Columns(columntouse).Select
ActiveSheet.Paste

Hope this helps,
-Chad

"hello" wrote:

I have many colums of data on one worksheet. Each column has two header rows
that describe the data type (for example, "demand" and "production").

On a separate sheet, I would like to lookup and copy the data in all of the
demand columns (and on another sheet, all of the production columns). It is
easy enough to use hlookup to copy the value in a single cell, but how would
I get the whole column?

Thank you