access visual basic - Excel VBA

Anne,
Try the code below.
Note that I've assumed that the Consolidated workbook is empty of data when the import is done. If you want to handle the case where the import is done incrementally, then you'll need to make some modifications regarding worksheet names, and the row number on the Consolidated worksheet.

Files attached for download.

regards
/Roy MacLean
--------------------------------------------------------------
Option Explicit

Sub ImportData()
'Import data from multiple workbooks called "Centre n" for n = 1, 2, ...
'Each source workbook has a single worksheet called "Marks",
'which is copied to this workbook.

On Error GoTo handler

Dim source_wbname As String 'Centre n workbook
Dim destination_wbname As String 'This workbook
Dim wscount As Integer 'number of worksheets in this workbook
Dim i As Integer

destination_wbname = ActiveWorkbook.Name
wscount = Worksheets.Count

i = 1
Do While True 'Do 'forever'; Exit loop when error occurs

source_wbname = "Centre " & i & ".xls"
Workbooks.Open (source_wbname) 'Becomes active

ActiveWorkbook.Worksheets("Marks").Copy _
after:=Workbooks(destination_wbname).Worksheets(wscount)

Workbooks(source_wbname).Close 'This workbook becomes active

ActiveWorkbook.Worksheets("Marks").Name = "Centre " & i

wscount = wscount + 1
i = i + 1
Loop

Exit Sub

handler:
Dim errnum As Integer 'Error number - identifies the type of error

errnum = Err.Number
If errnum = 1004 Then ''No Object' error
MsgBox "Import Completed"
Else 'Any other error
'Output the associated error message
MsgBox "Error: " & errnum & vbCrLf & Error(Err.Number)
End If

End Sub

Sub Consolidate()
'For each "Centre n" worksheet, copy the Totals range to the "Consolidated" worksheet,
'putting the Centre name in column A.

Dim wscount As Integer 'Number of worksheets in this workbook
Dim i As Integer
Dim wsname As String 'Name of the current "Centre n" worksheet

wscount = Worksheets.Count - 2 'minus 2 for the sheets "Front" and "Consolidated"

For i = 1 To wscount
wsname = "Centre " & i

Worksheets("Consolidated").Range("A" & i).Value = wsname

Worksheets("Consolidated").Range("B" & i & ":C" & i).Value = _
Worksheets(wsname).Range("Total").Value
Next


End Sub