referencing many closed workbooks

Hi Guys,

I have a workbook for each day recording energy usage from 52 meters,

I need to collate the data on a single workbook.

The only way i can think of doing it is to use the INDIRECT function but this will not work on closed workbook.

I need to sum the contents of B1:AW1 on the closed workbook and input that into a cell on the new sheet. then repeated for B2:AW2, B3:AW3 up to B52:AW52 once this has been done the process needs to be repeated for 31 workbooks.

The end product should be 31 Columns (one for each day ini the last month) and 52 rown (one for each meter) and the total daily consumption for each meter (sum of column B:AW)

I have no idea how this is going to happen but I think VBA may be the only solution.

thank you for reading and even more thanks for helping

This is not meant as a solution but just a start to give you an idea.
1. You need to create the array of workbook names,
2. You will need to control the starting row in the master where the sum will be entered.

Sub GetClosedSums()
    Application.ScreenUpdating = False
    Dim WorkbookList, _
        workbookToOpen, _
        NumBooks            As Long, _
        RangeSum            As Long, _
        FilePath            As String
        
    FilePath = "C:\Documents and Settings\Benjamin\Desktop\"
    
    WorkbookList = Array("the closed one")        '   <----enter your list here
    NumBooks = UBound(WorkbookList)
    Range("A1").Select
    For Each workbookToOpen In WorkbookList
        Workbooks.Open Filename:=FilePath & workbookToOpen
        RangeSum = WorksheetFunction.Sum(Range("B1:AW1"))
        
        ActiveWindow.Close
        Range("A1").Value = RangeSum
        ActiveCell.Offset(1, 0).Select
    Next workbookToOpen
    Application.ScreenUpdating = True
End Sub

You should provide a workbook with sample data. Since we don't know what your workbooks look like, the macro was just a hint of a possible solution.

The macro that I provided above is basically a loop. To use it, you will have to modify the line:

FilePath = "C:\Documents and Settings\Benjamin\Desktop\"

to reflect the drive and directories where your closed workbooks are found.

Next, the line:

WorkbookList = Array("the closed one")

would be modified to include the names of the workbooks you wish to open. Something like:

WorkbookList = Array("book1","book2","book3")

I think this may be close to what you are looking for.

Option Explicit
Option Base 1
Sub GetClosedSums()
    Application.ScreenUpdating = False
    Dim RangeSum        As Long, _
        FolderList      As Range, _
        SystemList      As Range, _
        SumRange        As String, _
        FileRoot        As String, _
        FName           As String, _
        SumAddress      As String, _
        FolderName      As Variant, _
        SubSystem       As Variant, _
        SUMMARYSHEET

    FileRoot = "C:\Directory\"
    Set SUMMARYSHEET = Workbooks("30 day SUMMARY.xls").Sheets("sheet1")
    Set FolderList = Sheets("sheet1").Range("B7:AG7")
    
    For Each FolderName In FolderList
        If Len(Dir$(FileRoot & FolderName, vbDirectory)) <> 0 Then
            FName = FolderName.Offset(1, 0).Value
            If Len(Dir$(FileRoot & FolderName & "\" & FName & ".xls", vbNormal)) <> 0 Then
                Workbooks.Open FileName:=FileRoot & FolderName & "\" & FName & ".xls"
                Set SystemList = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
                For Each SubSystem In SystemList
                    SumAddress = "B" & SubSystem.Row & ":AW" & SubSystem.Row
                    RangeSum = WorksheetFunction.Sum(ActiveSheet.Range(SumAddress))
                    SUMMARYSHEET.Cells(SubSystem.Row + 10, FolderName.Column) = RangeSum
                Next SubSystem
                Workbooks(FName & ".xls").Close False
            End If
        End If
    Next FolderName
    Application.ScreenUpdating = False
End Sub

Note that the subfolders must be in "C:\DIRECTORY"