Help needed to consolidate variable ranges in excel vba

[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

 

[KL]

Hi Rich,

Hola Noe´s,

Assuming that:

1) the first sheet is where you want to consolidate the rest of sheets
2) all sheets have the same data structure (number and order of columns,
data types, etc.)
3) data start at row 2 in all sheets

....try the code below

Regards,
KL

Sub Consolidate()
Dim ws As Long, wsFinal As Worksheet
Dim rngOrig As Range, rngDest As Range
Dim r As Long, rLast As Long, cLast As Integer

Application.ScreenUpdating = False
With ThisWorkbook
Set wsFinal = .Worksheets(1)
cLast = wsFinal.Columns(256).End(xlToLeft).Column
For ws = 2 To .Worksheets.Count
r = wsFinal.Range("A65536").End(xlUp).Row
With .Worksheets(ws)
rLast = .Range("A65536").End(xlUp).Row
Set rngOrig = .Range(.Cells(2, 1), _
.Cells(rLast, cLast))
End With
With wsFinal
Set rngDest = .Range(.Cells(r + 1, 1), _
.Cells(r + rLast - 1, cLast))
End With
rngDest.Value = rngOrig.Value
Next ws
End With
Application.ScreenUpdating = True
End Sub

 

[KL]

Please disregard the second line of my previous message as I accidentally
copied it from another one in Spanish.

KL

 

[Rich]

Thanks for your rapid response..however I don't think what you've written
will help.

I've been using the consolidate method as it appears to be the only way to
combine the data in each sheet. The problem I have is that the number of
rows and headers in each worksheet varies.

However, some rows and columns are the same, so the consolidate method pulls
all the data into one big table (and sums up any numbers which are appearing
in two separate sheets).

What I really need to solve my problem is a way of passing the ranges to the
Sources:= variable in the Consolidate Method.

Is there a way in which I could create a string that reads in the correct
format for the Sources:= Array(...)


Thanks again

Rich

 

[Jim Cone]

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 before:=Worksheets(1)
ActiveSheet.Range("B3").Consolidate Sources:=Array(RawData()), _
Function:=xlSum, LeftColumn:=True, TopRow:=True
Set Report = Nothing
End Sub
'-------------------------------


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

 

[Andy Wiggins]

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)