Did you look at Ron's code?

hkappleorange wrote:

Thanks all of you, but the data is in the form of 200 separate tables in 200
separate files.

Anyone has a better solution ?? Help....

"bj" ¼¶¼g©ó¶l¥ó·s»D
...
if you only have to do it once
copy and paste may be the simplest

another way would be to use the indirect() function

if you have a list of all of the file and worksheet names
in column A (or A and B) insert the list(s) or workbooks and worksheets
You may have to play with them a bit to get the format proper for the
indirect() function

and if the max number of records is 300 in any file
copy your list and fill down so that you will have 300 duplicates of the
list

Sort the columns
in B1 enter
=indirect(A1&"A"&mod(row(),300))
in C1 enter
=indirect(A1&"B"&mod(row(),300))
and in D1 enter
=indirect(A1&"C"&mod(row(),300))
And copy these and paste to the bottom of your list
Select columns B,C and D and copy and paste special values on top of
themselves.
Use autofilter and select blanks and delete to get rid of the extra rows.

you can also write a macro to do a copy and paste from all of the files.




"hkappleorange" wrote:

I have a simple task on Excel but I cannot figure it out. Pls help:-

I have 200 separate Excel files with data all in 3 columns: Region,
Month &
Amount. They each has a range of records from 100 to 300 lines each.

I am asked to create a single 3 column table with these 3 fields
containing
all data from these 200 tables in separate files.

How many ways we can do this ? Please help.....





--

Dave Peterson