I got a spreadsheet containing purchase information for a number of different contracts, each supplier in column A and the date of purchases in 1st row. I need a means of consolidating only a given range of the sheets into a combined summary, the needed sheets are consecutive and so I came up with the following:
But I get error 438
Any help would be appreciated
Code:
Sub Consolidation(sstart As String, send As String)
Dim aryShtNames() As Variant
Dim arySource() As String
Dim i As Long
Dim j As Integer
Dim HowManyEntries As Long
Application.ScreenUpdating = False
Sheets(sstart).Select
HowManyEntries = 0
Do
HowManyEntries = HowManyEntries + 1
ReDim Preserve aryShtNames(1 To HowManyEntries)
aryShtNames(HowManyEntries) = ActiveSheet.Name
j = ActiveSheet.Index + 1
Sheets(j).Select
Loop Until ActiveSheet = Sheets(send)
ReDim arySource(LBound(aryShtNames) To UBound(aryShtNames))
For i = LBound(aryShtNames) To UBound(aryShtNames)
arySource(i) = "'[" & fname & ".xls" & "]" _
& aryShtNames(i) & "'!R4C1:R260C78"
Next i
Sheets("Raw Data").Cells("A4").Consolidate Sources:=arySource, Function:=xlSum, _
TopRow:=True, LeftColumn:=True, CreateLinks:=False
Application.ScreenUpdating = True
End Sub
But I get error 438
Any help would be appreciated