R
Rich
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
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