for each worksheet ...

[Bartek]

Hi all,
briefly saying,
I need to construct a sets of date coming from two workbooks.
Each workbook has identical collection of sheets(same names) with
different data.
I would like to run a procedure that for each worksheet in Workbook1
copies a range of data into Workbook "SUM" and the same range out of
respective worksheet from Workbook2 again into Workbook "SUM"
Then after I have data from them both I can do sth. (create graph) and
then go to next worksheet in Workbook1

Oh wow hope it is OK to understand
thanks in advance,
Bartek

 

[Dave D-C]

briefly saying,
I need to construct a sets of date coming from two workbooks.
Each workbook has identical collection of sheets(same names) with
different data.
I would like to run a procedure that for each worksheet in Workbook1
copies a range of data into Workbook "SUM" and the same range out of
respective worksheet from Workbook2 again into Workbook "SUM"
Then after I have data from them both I can do sth. (create graph) and
then go to next worksheet in Workbook1

Oh wow hope it is OK to understand
thanks in advance, Bartek

I hope this is a usefull start:
Sub SubName()
Dim nRow&, iCol%
Dim WB1 As Workbook, WB2 As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet
Workbooks("SUM.xls").Activate ' Totals
nRow = 5 ' number of data rows
Set WB1 = Workbooks("Book1.xls")
Set WB2 = Application.Workbooks("Book2.xls")
iCol = 1
For Each WS1 In WB1.Worksheets
Set WS2 = WB2.Worksheets(WS1.Name)
Cells(1, iCol) = WS1.Name ' sheet name
WS1.Cells(1, 1).Resize(nRow, 1).Copy _
Cells(2, iCol) ' from book1
WS2.Cells(1, 1).Resize(nRow, 1).Copy _
Cells(2, iCol + 1) ' from book2
iCol = iCol + 2
Next WS1