Importing a Single Text file into multiple worksheets?

[nchokies]

Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's "columns" and written to the appropriate worksheet. The file is "!" delimited and has 11 columns for each row.

Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.

Any help anyone could provide would be WONDERFUL. Thanks!

 

[BrianB]

Hopefully my code here will help you get started - although it was written to answer another problem it should work.

http://www.mrexcel.com/forum/showthread.php?p=761632

However, your method does seem against the principle that it is best to keep data in a single table, when the powerful data functions etc. can be used. Personally I would put the data into an Access table. It is not too difficult to get to get Excel to use it from there, assuming Access itself is not better. In actual practice when using big files I generally do a lot of work in Access and then export to a .xls file for pretty formatting of reports.