Don,

It should be fairly easy to write this macro, but I have one question before
attempting it: Do you want the identifier from cell B2 in the first cell of
the seven rows (column A), and the data copied to columns B to K? Also, when
you say rows X to X+7, that is actually eight rows - do you want seven rows
(X to X+6) or eight rows (Z to X+7)?

HTH,
Bernie
MS Excel MVP


"Don" wrote in message
...
Hi All,

I would appreciate some help or advice with this problem please.
I have about 500 workbooks all named e.g. ABC123MyCompany, or
DEF234MyOtherCompany etc. The numerical part of the name may be 3 to 5
digits.

The sheets (only one per book) named with the AlphaNumeric part of the
workbook e.g ABC123 I need to append to a new table (not yet defined)
Cell B2 as an identifier and then the cells that contain certain data
which is in a consistent format in rows x to x+7 and A to J inclusive.

First problem is that Row X is variable in every sheet but the row
above always contains the string "Section 3 - Further Information to
be completed"

In my laymans term the logic is as follows-

Start at Cell A1- Move to A2 - Does this cell contain the
String "Section 3 - Further Information to be completed" -
If yes then copy the next 7 rows A to J to a
new worksheet
If No then move to cell A3 and repeat the process moving down the
sheet until the String is found

Then open the next workbook and repeat but appending the data to the
newly created sheet

The objective would be to create a single sheet containing the 7 rows
with the identifier of the 500 workbooks.
If it is easier to use the filename as an identifier instead of CellB2
then that would be acceptable. There is no problem with deleting the
workbook after importing as I would be using a copy of the original
data.

Further processing on the sheet would tidy any anomolies

Thanks in advance

Don