Summary sheet
 

Hello, any help on the following would be appreciated...

I want to create a sheet that has formulas which refer to the same ranges in
a varying number of workbooks in a specified folder. I am just learning VBA
so I am not sure the best approach.

Thanks in advance.
--
Ariel Dugan
Assistant Manager
Down To Earth Natural Foods
808-947-7678 Phone
808-943-8491 Fax
808-282-5916 Cell

Summary sheet
 

Sub AddFormulas()
Dim bk as Workbook, sh as Worksheet
Dim i as Long, fName as String
' create a new one sheet workbook
set bk = Workbooks.Add(Template:=xlWBATWorksheet)
' set a reference to a sheet in the book
set sh = bk.Worksheets(1)
' mark the first row where you want a formula
i = 1
' get the name of the first workbook in the
' directory
fname = Dir("C:\MyFolder\*.xls")
do While fName < ""
' build the linking formula
fName ="='C:\MyFolder\[" & fName & "]Sheet1'!$B$9"
' place the formula in the cell (F1 is the first in this example)
sh.cells(i,"F").Formula = fName
' increment the row counter
i = i + 1
' get the next filename
fName = dir()
Loop
bk.SaveAs "C:\MySummaries\MySummary1.xls"
End Sub

--
Regards,
Tom Ogilvy




"AD108" wrote in message
...
Hello, any help on the following would be appreciated...

I want to create a sheet that has formulas which refer to the same ranges
in
a varying number of workbooks in a specified folder. I am just learning
VBA
so I am not sure the best approach.

Thanks in advance.
--
Ariel Dugan
Assistant Manager
Down To Earth Natural Foods
808-947-7678 Phone
808-943-8491 Fax
808-282-5916 Cell

Summary sheet
 

Hi AD108

Maybe this one
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"AD108" wrote in message ...
Hello, any help on the following would be appreciated...

I want to create a sheet that has formulas which refer to the same ranges in
a varying number of workbooks in a specified folder. I am just learning VBA
so I am not sure the best approach.

Thanks in advance.
--
Ariel Dugan
Assistant Manager
Down To Earth Natural Foods
808-947-7678 Phone
808-943-8491 Fax
808-282-5916 Cell