Hi all,

I have in a directory "summary" some 800 files with a number for a name,
like 8, 23, 44, 124, 2045 up to somewhere in the 2500's. There is no list of
all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the
last cell used will be a cell in column H. I also know that the content of
that cell will be ==== (4 to 7 times =). Column H will contain text, blanks
(empty cells), numbers and other characters (like f.i. =). The number of
rows used will be at least 6 and could be as much as 200 or so (25 is
typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name
XYZ, sheet name KLM). Therefore the files of the summary directory have to
be taken one after each other (I cannot call them by name because there is
no system in the way the number names follow each other, so it has to be
"first file", "next file" etc.) and have the content of their only sheet
copied (appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the
entry "firstCNR" (without the ") followed by the number that is the name of
that file (so for instance firstCNR23 or firstCNR2045). After the last cell
in column H (which can only contain a number of = characters), so one cell
below the last cell used in column H, I want to put "lastCNR" (without the
") followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it
is appended two rows below to what already consists on sheet KLM of the new
workbook XYZ.

If the operation is completed all "number files" are back in the "summary"
directory, but slightly modified with first CNR... and lastCNR.. In sheet
KLM of workbook XYZ colums A to H we have then the some 800 files (slightly
modified) of "summary" below one other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands