We would do it in two steps:

1
Labels
copy header rows from each spreadsheet into column A of final worksheet
(fws)
sort,
delete duplicates 'this ensures no misspell label is missed out
put in row1 as label

This can be automated but does not worth the effort.

2
Now read next available row number in fws
read each column label in worksheet in each workbook
use HLookUp to match column in fws
tug data of this column accordingly
read next column label
increment row number
do for next workbook

Cheers

"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only common
in some of the 12 and there are few column names not common but just in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.