Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

access visual basic - excel vba

Forum home » Delegate support and help forum » Microsoft VBA Training and help » access visual basic - Excel VBA

access visual basic - Excel VBA

ResolvedVersion Standard

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

Edited on Mon 1 Oct 2007, 11:58

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

Attached files...

Centre 1.xls
Centre 2.xls
Consolidated_initial.xls

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

VBA tip:

Use VbNullString instead of

When needing to default a String variable back to it's default of "" use vbNullString as in the following example:

Sub ClearText()

Dim strEmpName As String

strEmpName = "John Smith"
MsgBox strEmpName

strEmpName = vbNullString
MsgBox strEmpName

End Sub

View all VBA hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.