Take the same column from multiple worksheets to create a new table

[roberino]

The instruments I use have created 100 data files that take a measurement every few hundredths of a second for twenty channels at a time for 100 seconds.

Time a b c d e f etc...
0.01 1 0 1 2 5 7
0.03 5 1 1 3 4 2
0.05
0.07
etc...

I have combined these into a single workbook, but now I need to reformat the data into one sheet per each channel (column). This would leave me with 20 sheets. I need a quick way to take all of the data from the same column on every sheet and put it into a table so that the data from the next sheet appears in the next column (like below). Is there any easy way to do this?

Time a1 a2 a3 a4 a5 a6 etc...
0.01 1 2 3 5 1 2
0.03 5 6 3 1 4 6
0.05
0.07
etc...

The sheets are numbered XXXX 001, XXXX 002.

[ragulduy]

Please Login or Register  to view this content.

[roberino]

Thanks for the quick reply! Sorry, but I don't have a clue when it comes to this stuff. Do I write this in as a Macro? Also, what are the variables in this script?

[ragulduy]

I've rewritten it with variables:

Please Login or Register  to view this content.

Yes you need to run it as a macro. Press alt+f11 to open the vba editor, copy and paste into a standard module (insert->module) and then press f5 to run.