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