Hello!

I have 5 workbooks which contain data: GROUP1.xls ...
GROUP5.xls. {NOTE: These names are not cast in stone; I
can change them if needed to solve this problem. Read
on.} Each workbook contains 5 worksheets. The books are
identical in structure, i.e. the names of the 5
worksheets are the same in all 5 workbooks, only the data
on the sheets is different.

I want to use a 6th workbook with VBA code to extract and
combine data from the 5 workbooks (at times only two, but
other times, 3, 4 or 5), but I can't figure out how to do
it with a loop because I can't get the syntax correct.

For example, assume cells A1 of Sheet1 of the 2,3,4 or 5
workbooks contain a number. I want Cell A1 of the 6th
book to contain the sum of those numbers. So I want to
loop through each of the five, add the numbers together,
and display the total in Cell A1 of the 6th book.

I need something which will allow me to VARY the name of
the workbook I'm extracting from but still have VBA
recognize it. Here is what I mean:

NumberBooks = {2, 3, 4 or 5}
Dim MYSHEET, SixthBook as Object
Set SixthBook = {active workbook sheet}
For N = 1 to NumberBooks
Set MYSHEET&N = Workbooks(Group&N.xls).Sheets("SheetName")
SixthBook.Cells(#,#) = Workbooks(Group&N.xls).Sheets_
("SheetName")
{addition operation}
Next N
{display operation}

I know this isn't right, but hopefully you understand
what I mean. I want to go from book to book using a for-
next loop with the book name as a variable.

Anybody know of a slick way to do this? Help is
appreciated.

OBTW: I currently have a hard-coded version of this
operation which I had to complete to meet a deadline. It
took 1547 lines of code. I have another project coming
which will be similar, but different enough where I'll
have to start from scratch. I'd like to avoid hardcoding
again, hence the question. Thanks for your time.

MARTY

The only thing I see "wrong" is that the Dim statement should be:

Dim SixthBook as Worksheet

The way that the code is written, all of the workbooks need to be ope
when you start, right?

If all of the books are open when you start, then you might not want
For ... Next loop. Maybe you want to use For Each.

For Each Window In Application.Windows
Window.Activate
Next Windo

--
Message posted from http://www.ExcelForum.com

Yes, all of the workbooks will have to be open.

If I use a For Each, it will also try to activate the 6th
book (which is also open), and access its cell A1 also,
which I don't want it to do. Also, I need to use a For
Next for other purposes in the routine.

To boil it down, I guess my REAL question is this: if N
is an integer, is this syntax (&N) allowed in workbook
names, objects, etc. Example:

Set MYSHEET&N = Workbooks(Group&N.xls).Sheets("SheetName")

If N = 1, will VBA interpret this as:

Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName")

?????

Thanks,
MARTY
-----Original Message-----
The only thing I see "wrong" is that the Dim statement
should be:

Dim SixthBook as Worksheet

The way that the code is written, all of the workbooks
need to be open
when you start, right?

If all of the books are open when you start, then you
might not want a
For ... Next loop. Maybe you want to use For Each.

For Each Window In Application.Windows
Window.Activate
Next Window


---
Message posted from http://www.ExcelForum.com/

.

Marty,

The first sub below calls the second, which sums the value from the sheet,
cell, and array of "GROUP?.xls" depending on what is passed to it. The
example will sum Sheet1 A1 from GROUP2, GROUP3, GROUP4, and GROUP5.xls, and
places the sum into cell A1 (the same cell as that being summed) of the
active sheet of the workbook where the code is located.

The GetSum macro can be called as many times as you want. To call it to sum
cells A1:A10 from Sheet1 of Group2, 3, 4 and 5, you could do this:

Dim Books As Variant

Dim myCell As Range

Books = Array(2, 3, 4, 5)

For Each myCell In Range("A1:A10")
'Call the summing macro
GetSum "Sheet1", myCell.Address, Books
Next myCell

HTH,
Bernie
MS Excel MVP

Sub PutSum()
Dim Books As Variant
Books = Array(2, 3, 4, 5)

'Call the summing macro
GetSum "Sheet1", "A1", Books

End Sub

Sub GetSum(SheetName As String, _
CellAdd As String, _
BookNums As Variant)
Dim N As Integer
Dim mySum As Double

For N = LBound(BookNums) To UBound(BookNums)
mySum = mySum + Workbooks("GROUP" & BookNums(N) & ".xls"). _
Worksheets(SheetName).Range(CellAdd).Value
Next N
ThisWorkbook.ActiveSheet.Range(CellAdd).Value = mySum
End Sub


"MARTY" wrote in message
...
Hello!

I have 5 workbooks which contain data: GROUP1.xls ...
GROUP5.xls. {NOTE: These names are not cast in stone; I
can change them if needed to solve this problem. Read
on.} Each workbook contains 5 worksheets. The books are
identical in structure, i.e. the names of the 5
worksheets are the same in all 5 workbooks, only the data
on the sheets is different.

I want to use a 6th workbook with VBA code to extract and
combine data from the 5 workbooks (at times only two, but
other times, 3, 4 or 5), but I can't figure out how to do
it with a loop because I can't get the syntax correct.

For example, assume cells A1 of Sheet1 of the 2,3,4 or 5
workbooks contain a number. I want Cell A1 of the 6th
book to contain the sum of those numbers. So I want to
loop through each of the five, add the numbers together,
and display the total in Cell A1 of the 6th book.

I need something which will allow me to VARY the name of
the workbook I'm extracting from but still have VBA
recognize it. Here is what I mean:

NumberBooks = {2, 3, 4 or 5}
Dim MYSHEET, SixthBook as Object
Set SixthBook = {active workbook sheet}
For N = 1 to NumberBooks
Set MYSHEET&N = Workbooks(Group&N.xls).Sheets("SheetName")
SixthBook.Cells(#,#) = Workbooks(Group&N.xls).Sheets_
("SheetName")
{addition operation}
Next N
{display operation}

I know this isn't right, but hopefully you understand
what I mean. I want to go from book to book using a for-
next loop with the book name as a variable.

Anybody know of a slick way to do this? Help is
appreciated.

OBTW: I currently have a hard-coded version of this
operation which I had to complete to meet a deadline. It
took 1547 lines of code. I have another project coming
which will be similar, but different enough where I'll
have to start from scratch. I'd like to avoid hardcoding
again, hence the question. Thanks for your time.

MARTY