Help needed to consolidate variable ranges in excel vba
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
|