Load worksheet to array, load new worksheet with specific parts of array

I am looking for the most simple and/or effective way to load an entire worksheet into an array, and then selectively put data (by column) into a different workbook.
For example; lets say I have a worksheet with 15 columns and is X rows long. I put that data into an array. Now I only want to put the columns that I want into a new workbook - columns where the header = "Something Here".

I would load the array like this:

Please Login or Register  to view this content.

I know that I could loop through the array and do a Select Case to find the headers I want, like below, but that seems like it's more than it should be.

Please Login or Register  to view this content.

What I am looking for is something a lot more compact and simple... IF it's possible?

Please Login or Register  to view this content.

I'm assuming "Name" would have to be replace with some sort of FIND or SEARCH function.

It's pretty easy. If you place an example file. With Application.Index you can adjust the column order.

Please Login or Register  to view this content.

Originally Posted by Vraag en antwoord

It's pretty easy. If you place an example file. With Application.Index you can adjust the column order.

Please Login or Register  to view this content.

@Vraag But that does not seem to do anything that I'm asking for. I don't know where each column is at in the SOURCE file, therefore I can't do any reordering or "copy/paste" by static numbers. I need to be able to "search" the array header to find the correct column index.

You could use something like

Please Login or Register  to view this content.

Originally Posted by Fluff13

You could use something like

Please Login or Register  to view this content.

@Fluff13 That gets me a little closer - I see what you are doing is really just building a second array (ColAry) from the first (arrCopy) and then "pasting" it on the worksheet. That is great, as long as I have only a few columns, but what if I have to do a SELECT CASE for 5, 10, 15... column names?

Sure, I could also predetermine the column numbers using something like

Please Login or Register  to view this content.

but again that means I have to have X number of lines just to find each header index.

Is there no way to do a FIND or SEARCH in an array without iteration? I guess I'm just trying to figure out the fewest lines of code for this.

I would tend to stick with the Select Case, that way you only loop through the array once & will probably be the quickest way of doing it.