Anne has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced course
Excel VBA
Need to consolidate worksheets from a workbook onto a consolidated workbook.
Thanks.
Anne
RE: 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