I've been trying to find something that will help me with this but haven't found anything I can manipulate to suit my needs.
My problem is this:
I have many workbooks in a folder, lets say c:\temp\book1.xls, book2.xls...book100.xls
I have another workbook in c:\main.xls
Each of the workbooks in c:\temp contain different columns in different orders, but they all have one consistant column header labelled 'Phase' in the first row (the column number is not the same however).
main.xls also has a column labelled 'Phase'.
Also the column may occur more than once in a workbook.
What I'm looking for is a macro that I can run from main.xls, that will look through all of the workbooks in c:\temp, search the top row until it finds 'Phase' and copy all of the non blank entries in that column, and put them into the 'Phase' column in main.xls, one after another (essentially making one big list of all the phases from the workbooks in c:\temp.
Additionally, if possible, i would like it to put the name of the workbook it found the entries in next to it.
For example, main.xls would ideally look like this:
Is anyone able to help me out? This would be MUCH appreciated as it would save me soooo much time!