VBA to Merge Multiple Excel Files - But only output 2 columns by name

Hello,

Has been a while since I posted for some help.

I have multiple excel files with a ton of columns. I want to merge the multiple files into one spreadsheet, but only output the two columns I need.

- One of the columns has a fixed name across ALL the excel files: (PDH-CSV)
- The other column has a part of the name that is the same across all the excel files: TCP\Curr Estab
- The actual name on one of the spreadsheets is \\178.215.141.36\TCP\Curr Estab => I want to key off of TCP\Curr Estab

I have attached 2 sample files that need to be merged. I removed the majority of the columns, but left the problem/condition I am running into w/ the below script; The column I need is not always in the same place.

1. Sample File 1 (Col A - Col E).xlsx ==> Need Column A and Column E
2. Sample File 2 (Col A - Col G).xlsx ==> Need Column A and Column G

Final output would be:

|| Column A || Column B ||
|| (PDH-CSV) || TCP\Curr Estab ||
|| ?????????? || ?????????????? ||
|| ?????????? || ?????????????? ||
|| ?????????? || ?????????????? ||
|| ?????????? || ?????????????? ||
|| ?????????? || ?????????????? ||

The below script works OK, but it pulls in all the columns and the TCP\Curr Estab column is not always in the same place like the two sample files I attached so I get 'incorrect' values in the resulting output.

Please Login or Register  to view this content.

Thanks Again !!

Hi, as per forum rules, you need to use

Please Login or Register  to view this content.

[/noparse]

Do you only need the 2 specific columns to be shown? i.e. the rest of the data is not important, and will not be copied/merged.
And where should it be shown? In a new workbook? or in active workbook but in a new worksheet? or somewhere in the active worksheet?

- The actual name on one of the spreadsheets is \\178.215.141.36\TCP\Curr Estab => I want to key off of TCP\Curr Estab

What do you mean by this?

Try this macro, you can run it anywhere, a new workbook will be created

Please Login or Register  to view this content.

Thank You. Works very well !!!