Each month I receive a workbook with approx 70 wkshts in it. These
sheets are never in the same order and the sheets are only numbered "sheet1"
etc. The sheets are then identified and copied into one of seven existing
wkbks (each with approx 10 wkshts).

The 70 worksheets are identified because A1:A5 give details i.e. A4 or
A5 names the wkbk it is to be copied into and A2 or A3 names the actual
wksht it is to be copied to.

Could anybody help with this problem, indeed is it do-able

As I see it, I need to

Open one of the 7 existing wkbks as well as the new 70 sheet wkbk.
Ask it to compare page 1 A1:A5 with each of the 70 wkshts until it finds a
match.
Then copy the found sheet, back to the original (existing)
Then move to the next page and repeat until all 10 sheets from the 1st book
has been found.
Then open book 2 and repeat .

Any assistance gratefully received,

Bob

Might as well "deal out" the sheets all at once. Use code something like:

Dim sh as Worksheet
Dim sBook as String, sSh as String
Dim wkbk as Workbook
workbooks.Open "C:\bk1.xls"
workbooks.Open "C:\bk2.xls"
workbooks.Open "C:\bk3.xls"
workbooks.Open "C:\bk4.xls"
workbooks.Open "C:\bk5.xls"
workbooks.Open "C:\bkof70.xls"

for each sh in Workbooks("bkof70.xls).Worksheets
if instr(sh.Range("A4"),"xls") then
sBook = sh.Range("A4").value
sSh = sh.Range("A2").Value
else
sBook = sh.Range("A5").Value
sSh = sh.Range("A3").Value
end if
set wkbk = workbooks(sBook)
sh.Copy After:=wkbk.Worksheets(wkbk.Worksheets.count)
wkbk.Worksheets(wkbk.Worksheets.count).Name = sSh
Next

workbooks("bk1.xls").Close SaveChanges:=True
workbooks("bk2.xls").Close SaveChanges:=True
workbooks("bk3.xls").Close SaveChanges:=True
workbooks("bk4.xls").Close SaveChanges:=True
workbooks("bk5.xls").Close SaveChanges:=True

--
Regards,
Tom Ogilvy


Robert Gillard wrote in message
...
Each month I receive a workbook with approx 70 wkshts in it. These
sheets are never in the same order and the sheets are only numbered
"sheet1"
etc. The sheets are then identified and copied into one of seven existing
wkbks (each with approx 10 wkshts).

The 70 worksheets are identified because A1:A5 give details i.e. A4 or
A5 names the wkbk it is to be copied into and A2 or A3 names the actual
wksht it is to be copied to.

Could anybody help with this problem, indeed is it do-able

As I see it, I need to

Open one of the 7 existing wkbks as well as the new 70 sheet wkbk.
Ask it to compare page 1 A1:A5 with each of the 70 wkshts until it finds
a
match.
Then copy the found sheet, back to the original (existing)
Then move to the next page and repeat until all 10 sheets from the 1st
book
has been found.
Then open book 2 and repeat .

Any assistance gratefully received,

Bob