Selecting header rows with macro.

[John M.]

I would like to create a macro that selects certain "Columns" based on a
header name in an excel file. It would then copy all of that information
except the header into another existing excel file under different header
names. something like this:
In workbook 'A' I have 4 columns - "Name", "Description", "Price" and
"Quantity" I want to select all info under the headers "Name" and "Price" it
would then copy all of that information to workbook 'B'. Any info that was
under "Name" would go under the header "Item" and any info that was under
"Price" would go under the header "MSRP". I have no problem using macro to
copy from one workbook to the next, but I do have a problem selecting
multiple Columns because with the files that I get, the columns are not
always in the same place. If anyone has a solution to my problem I would be
very thankful. It would shave off 6 hours of tedious manual work so I could
focus on other projects.

 

[Joel]

I would put on a seperate worksheet the old column Name (book A)in Column A
and the new column name (Book B) in column B. This should get you started.

the use the following loop
RowCount = 1
with workbooks("A")
with .sheets("Column Names")
do while .Range("A" & RowCount) <> ""
OldCol = .Range("A" & RowCount)
NewCol = .Range("B" & RowCount)

with .sheets("Data")
set c = .Row(1).find(what:=OldCol, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find Column : " & OldCol)
exit sub
else
LastRow = .cells(Rows.Count,c.column).end(xlup).Row
Set CopyRange = .Range(c.offset(1,0),.cells(LastRow,c.column))
end if
end with
with workbooks("B").sheets("Data")
set c = .Row(1).find(what:=NewCol, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find Column : " & NewCol)
exit sub
else
CopyRange.copy Destination:=c.offset(1,0)
end if
end with
RowCount = RowCount + 1
loop
end with
end with