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

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

Chad -- Thank you for your reply. I was hoping I could get away with not
writing a macro, but if that's what I have to do, I'll give it a try.

Is there a good reference for the macro code you used?

Thanks again



"Chad" wrote:

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