Copying Specific Data sets and pasting them in Columns of 10

It would be easier if you sent a sample of the input data as well as its matching expected results in an excel file.

Here's an example of what I want. Sheet 1 shows how the data is exported and sheet 2 shows how I would like the data organized
ExampleTimedata.xlsx
Thanks for any help

run the macro. the output is recorded in sheet3

Hi rcm thanks for your input, but its not exactly what I need. I need only the distance traveled for each data set not the velocity. Also the data has to be organized where all the genotypes ie) all the Canton-S data are placed next to each other. So each column of 10 data points is next to another column of 10 data points from canton-s.

My bad. Ill fix in a second..

Take a look at it now!

It works beautifully!!!!. I have to ask though will this work if I have more than 3 genotypes? Also if you dont mind could you walk me through your code for learning purposes.
THANK YOU so much again

Please Login or Register  to view this content.

If you have any more questions, (ah yeah it will work for n groups), please feel free to ask...

I do have a few more questions (sorry). I am importing these data from different files is there a way to add this into the code as well. I tried to add this to your code in order to do this but it didnt really work heres my attempt.

Please Login or Register  to view this content.

I probably should clarify. I am adding data from several different files and I would like it if possible to have all the data from the different sheets compiled together into one worksheet.

In order to further help, I need to know more

All the *.xlsx files in the directory contain the data and need to be transferred.
what is the structure in each file the same as the output file?
is the data organized by groups too?

Yes all files are the same as the example data I had posted. I would like the data to be organized in the same way as before. It would just be a continuation of the same process.

So I was playing around with this today. I found a piece of code that will import the data, but i get an error code: Runtime-9 Error Subscript out range. Ill also post what I have so far using your code rcm

Please Login or Register  to view this content.

so what Im trying to do is select all the data and have it pasted in sheet1 one after the next that way I can use your code to sort and sequence it on sheet 3.
Thanks for any help

I think a good programming principle is that you use routines...

The reason to call format after all the data has been transfered is tha format will read the whole thing...

The fetching of the data should be done with a DIR loop to gather all the data from all the files in the specified directory (Thepath)

Please Login or Register  to view this content.

I copied your suggestion into the original code you had sent me and I got no result. I'm not sure if I am understanding you, are you saying I should have a folder where all the data is imputed and vba just accesses the data from that specific folder

My suggestion was just a sketch or the structure of things.

Either you have all the files in one common folder or you have a list full path included so it can read it from. I can help you write the full code either one. Send one sample of the original files to be appended and I will write the code from there.

Ahh ok I see. Yes I will have all the files placed into a common folder.Example data .xlsx The attached folder is on of many that have to be imported. I was also thinking about using a solution that another user Alf had given to me on a separate question I asked.

Please Login or Register  to view this content.

I was unable to get this to work for multiple files however

Thank you for your help thus far!

I coded a very simplistic routine that reads from a folder all normal files and places the pertinent data in sheet(1) of each file in sheet(3) of this workbook

please adjust the path to your needs

ah.. the routine call format at the end to extract the data and place it neatly in sheet2.

When trying it out make sure that both sheet2 and sheet3 are empty

After stepping through the code several times I got it to work perfectly. Sorry for asking a dumb question in the post above.
Heres what I did
I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Now I have to ask is there a way to display the name of the file above the data. Like in row A it marks the begging of each files data, by displaying the files name.

The shameless Bump...

yes there is, Ill walk you through..
.

Please Login or Register  to view this content.

Hi rcm,

I pasted the code, but I get the error Method 'Range' of object '_Global' failed. Thank you again for all your help thus far!

try... the error has something to do with rmax, rwk, sorry but I have not runned through,,

Please Login or Register  to view this content.

I probably should clarify I need the file information on sheet 3 above the already sorted data, in row 2 if possible. After that the code would be perfect

I probably should clarify I need the file name information on sheet 3 above the already sorted data, in row 2 if possible. After that the code would be perfect