create a list in file 1 with data from file 2,3,4...n

Hi.

I need to make a macro that makes an "intelligent" list (cant find any better word for it) in file 1 by using data in file 2.

The case:
I have several Excel files for calculation (called calculation file 1,2...n), each with a list of output data. I also have an Excel file (called main file) that uses all the output data from the calculation sheets. The calculation sheets are all the same, just with different input data.

The problem:
The number of caluculation sheets and therefore the name of these sheets change, thus I cannot make simply a normal reference in the main file.

What I need:
I am thinking that a "submit" button in each calucation file would work. Such that when you press it, the output data are written to the main file. However, the list then created needs to be "intelligent", such that I can press the submit button in each file and the data then are written at the bottom of the list.

Can someone help with this or point me in the correct direction?

I am fairly new to this, but from what I can see something in the lines of that should work. However, I get an error when running the script:

Object doesn't support this property or method.

For the two last lines in your code.

Got it working now. My mistake.

Thanks a lot!