This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants.
See how simple it is to consolidate any combination of your organisation's
accounts. (No VBA used)

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Rich" wrote in message
...
can anyone help? I want to be able to write a piece of vba code that will
automatically scan through a series of worksheets, identify the data
range,
and then consolidate each worksheet data range into one sheet.

The problem I have is the 'Consolidate Method' uses an Array for
determining
the ranges needed to consolidate.

I've written the following code..

Dim Report As Worksheet
Dim RawData(30) As Range
Dim a As Integer

a = 1

For Each Report In ThisWorkbook.Worksheets

Report.Activate

Range("a3").Select

RName = Report.Name

' following identifies data range

With Application.WorksheetFunction
r = 65537 - .CountBlank(Report.Range("A:A"))
c = 258 - .CountBlank(Report.Range("2:2"))
End With

Set RawData(a) = Report.Range(Cells(2, 1), Cells(r, c))

a = a + 1

Next Report

Worksheets("Sheet1").Range("B3").Consolidate _
Sources:=Array(RawData(1), RawData(2)..etc), _
Function:=xlSum, LeftColumn:=True, TopRow:=True

Many thanks in advance

Rich