Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
Summary Sheet | Excel Worksheet Functions | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) | |||
Summary sheet | Excel Programming | |||
Summary sheet help | Excel Programming |