As Alan intimates you'll need a VBA macro for this.

But a more fundamental question is why are you using 1000 workbooks? From where does the data come, how are they updated and by whom? 1000 different people?
Once all the data was combined into a single workbook could you see new data being added to the single workbook instead?

In other words should our effort be concentrated into getting stuff into a single more easily manageable workbook (and don't assume tnat necessarily means 1000 worksheets in the single wb), or providing something that has to iterte through 1000 workbooks each day?