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
"KL" wrote in message
...
Please disregard the second line of my previous message as I accidentally
copied it from another one in Spanish.

KL

"KL" wrote in message
...
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