Excel

Hiral,
We looked at this with the last example of the course: merging data from various source workbooks (Centre1, Centre2, ...) into a single workbook (Consolidated).

The example relies on all the source workbooks having names that can be constructed as "Centre" & i, for i = 1, 2, ..., inside a loop. The body of the loop is:

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

We used the file-not-found error to jump out of the loop - again, see the notes.

Note that when using multiple workbooks, life is simpler if they are all in the same folder on disk.

Hope this helps,
/Roy MacLean