I have a folder with around 550 lenthy files which I wish to split into 2 files each. All files are in Excel 2003 format and have similar data structure and only 1 worksheet each. Column A contains dates in the format dd-mmm-yy like 31-May-12, 1-Jun-11 etc. The macro should look for a particular date value "30-Dec-04" (please note that in the formula bar, this value reads as 30/12/2004) and when this value is found, split the original file (from A1 position) upto this row as file 1 and from next row (of this date value) till the end of the file as file2. Then delete the original file. For example, if the original file name was ABC.xls then the two new files would be ABC1.xls and ABC2.xls.
I hope my problem is clear and there is no confusion. Help in this respect would be highly appreciated.