Find and Copy ?
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 |
Find and Copy ?
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 |
All times are GMT +1. The time now is 11:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com