Rich,

Comments and clarification...
The source array must be a string and include the full address using R1C1 notation.
The sheet to receive the consolidated data must not overlap any of the data being consolidated.
Variables r and c should be declared.
Using countblank as you have won't always give you the last cell.
See if the following code makes sense and works for you.

Regards,
Jim Cone
San Francisco, USA

'----------------------------------
Sub NewsPostTest()
Dim Report As Excel.Worksheet
Dim RawData() As String
Dim a As Long
Dim r As Long
Dim c As Long
ReDim RawData(1 To Worksheets.Count)
a = 1

'For Each Report In ThisWorkbook.Worksheets
For Each Report In ActiveWorkbook.Worksheets
Report.Activate
Range("a3").Select

'following identifies data range
r = Report.Cells(Rows.Count, 1).End(xlUp).Row
c = Report.Cells(2, Columns.Count).End(xlToLeft).Column
RawData(a) = Report.Range(Cells(2, 1), Cells(r, c)) _
.Address(ReferenceStyle:=xlR1C1, External:=True)
a = a + 1
Next 'Report

Worksheets.Add befo=Worksheets(1)
ActiveSheet.Range("B3").Consolidate Sources:=Array(RawData()), _
Function:=xlSum, LeftColumn:=True, TopRow:=True
Set Report = Nothing
End Sub
'-------------------------------


"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