I have list of about 150 spreadsheets in this folder that I have to iterate through, open, grab numbers from 20 different places or so and put those numbers on a second spreadsheet (which will comprise a "Summary" of the individual spreadsheets).

Zipping through each file in the folder and grabbing the numbers was easy. Then, they through a monkey wrench at me.

In one column on each sheet, there are three rows with different numbers in them. Originally, I was just pulling all three numbers. Now, however, they want me to total these three and bring back just the one total. That is where I'm having a problem. I Googled around and found WorksheetFunction.Sum and figured that would solve my problem. However, I get the error
Quote Originally Posted by Error
Run-time error '1004'

Unable to get the Sum property of the WorksheetFunction Class
Here is the code (NumFiles is a Function I wrote the counts the number of files in the folder and is not related to this issue)
Code:
Dim MarketBasedTemplate As Workbook
Dim HFRSheet As Workbook
Dim intRow As Integer

Set MarketBasedTemplate = Workbooks.Open("\\sntXXX\sharename\Market Based\MarketBasedTemplate.xls", UpdateLinks:=0)
intRow = 4  'start of data feed
Dim lngOtherTotal As Long
Label3.Caption = NumFiles("\\sntXXX\sharename\Market Based\Standard_HFR\") & " to process"
NumStandFiles = NumFiles("\\sntXXX\sharename\Market Based\Standard_HFR\")
ListBox1.Clear
strLoad = Dir("\\sntXXX\sharename\Market Based\Standard_HFR\*.xls")
ChDir "\\sntXXX\sharename\Market Based\Standard_HFR"
Do While strLoad > vbNullString
   DoEvents
   NumStandFiles = NumStandFiles - 1
   ListBox1.AddItem strLoad
   Label3.Caption = NumStandFiles & " left to process"
   Set HFRSheet = Workbooks.Open("\\sntXXX\sharename\Market Based\Standard_HFR\" & strLoad, UpdateLinks:=0)
   HFRSheet.Sheets("Comments").Select
        HFRSheet.Sheets("Comments").Range("D7").Copy  'provider code
        MarketBasedTemplate.Sheets("Sample Output").Range("A" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
        HFRSheet.Sheets("Comments").Range("D9").Copy  'fye
        MarketBasedTemplate.Sheets("Sample Output").Range("B" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
                
        HFRSheet.Sheets("HFR Setting").Select  'switch to data sheet
        
        HFRSheet.Sheets("HFR Setting").Range("I21").Copy  'ip operating cost
        MarketBasedTemplate.Sheets("Sample Output").Range("C" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
        HFRSheet.Sheets("HFR Setting").Range("I24").Copy  'ip capital
        MarketBasedTemplate.Sheets("Sample Output").Range("D" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
        HFRSheet.Sheets("HFR Setting").Range("I23").Copy  'ip gme
        MarketBasedTemplate.Sheets("Sample Output").Range("E" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
        HFRSheet.Sheets("HFR Setting").Range("I25").Copy  'ip bad debt
        MarketBasedTemplate.Sheets("Sample Output").Range("F" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
'---------------- below is what Im trying to get to work                
  lngOtherTotal = Application.WorksheetFunction.Sum("I26:I28") 'total the Other Categories
         MarketBasedTemplate.Sheets("Sample Output").Range("G" & intRow) = lngOtherTotal
'more code that is similiar to this all the way across to Column W 
'everything works but above
   HFRSheet.Close
    intRow = intRow + 1
   strLoad = Dir
Loop