Different Number of Workbooks each week
 

Greetings all.

I have built a spreadsheet that is linked to multiple spreadsheets and sums
up information from each spreadsheet

Example:

MasterSheet.xls

In Cell A1 on Sheet 1 it sums up Cell A1 From Sheet one of 8 Workbooks
called

User1.xls
User2.xls
User3.xls
User4.xls
down to User8.xls

This works fine and the workbook calculates correctly. But the formula is
long and bulky and I was wondering if there is a way to do the formula so
that if only 6 spreadsheets are in the folder that it will still do the sum
of Cell A1 from Sheet1 of each workbook or if I added a 9th Ulser9.xls file
into the folder that it would calculate that in without me having to go back
in to the spreadsheet and changing a plethora of formulas in many cells.

If I can't do this with a Formula, is there a way to do it with VBA to say
look for any files with User*.xls and sum cell A1 from Sheet 1

Any help would be appreciated.

Wally Steadman

Different Number of Workbooks each week
 

Hi Wally,

Why you dont use :
sub values()
for r = 1 to 9
with range("A1")
.FormulaArray = "='e:\[user" & r & ".xls]" & sheets(1).name & "!A1"
.value=.value
end with
next r
end sub

Regards,

halim

Wally Steadman wrote:
Greetings all.

I have built a spreadsheet that is linked to multiple spreadsheets and sums
up information from each spreadsheet

Example:

MasterSheet.xls

In Cell A1 on Sheet 1 it sums up Cell A1 From Sheet one of 8 Workbooks
called

User1.xls
User2.xls
User3.xls
User4.xls
down to User8.xls

This works fine and the workbook calculates correctly. But the formula is
long and bulky and I was wondering if there is a way to do the formula so
that if only 6 spreadsheets are in the folder that it will still do the sum
of Cell A1 from Sheet1 of each workbook or if I added a 9th Ulser9.xls file
into the folder that it would calculate that in without me having to go back
in to the spreadsheet and changing a plethora of formulas in many cells.

If I can't do this with a Formula, is there a way to do it with VBA to say
look for any files with User*.xls and sum cell A1 from Sheet 1

Any help would be appreciated.

Wally Steadman

Different Number of Workbooks each week
 

Halim,
thanks for your reply. I have a couple of questions about it.

1. Where to I apply the sub to? The Master Worksheet?

2. I will be doing the same summing for multiple cells but all like cells.
So B1 in the master will be the sum of B1 on all worksheets. I actually
have an Array that is from
A1 to O27 and so each cell in the array is summed to like cells in each
workbook. So Do I need this code to follow a couple FOR loops to have it
sum each cell?

I am tracking with what it is doing, just not sure where to apply and have
some kind of idea as to the FOR loops you have shown.

"halim" wrote in message
ups.com...
Hi Wally,

Why you dont use :
sub values()
for r = 1 to 9
with range("A1")
.FormulaArray = "='e:\[user" & r & ".xls]" & sheets(1).name & "!A1"
.value=.value
end with
next r
end sub

Regards,

halim

Wally Steadman wrote:
Greetings all.

I have built a spreadsheet that is linked to multiple spreadsheets and
sums
up information from each spreadsheet

Example:

MasterSheet.xls

In Cell A1 on Sheet 1 it sums up Cell A1 From Sheet one of 8 Workbooks
called

User1.xls
User2.xls
User3.xls
User4.xls
down to User8.xls

This works fine and the workbook calculates correctly. But the formula
is
long and bulky and I was wondering if there is a way to do the formula so
that if only 6 spreadsheets are in the folder that it will still do the
sum
of Cell A1 from Sheet1 of each workbook or if I added a 9th Ulser9.xls
file
into the folder that it would calculate that in without me having to go
back
in to the spreadsheet and changing a plethora of formulas in many cells.

If I can't do this with a Formula, is there a way to do it with VBA to
say
look for any files with User*.xls and sum cell A1 from Sheet 1

Any help would be appreciated.

Wally Steadman