Hi Howard,

Am Mon, 18 Nov 2013 23:55:59 -0800 (PST) schrieb Howard:

I have tried this worksheet array and the new problem with it is that I only get the Sheet 3 data copied into Sheet 4.

I'm thinking the advantage here is that the sheet selection and the sheet order can be adjusted in the array.

Say Worksheets(Array("Sheet3", "Sheet6", "Sheet1")) (Omitting sheets 1, 2 from the copy to sheet 4)

with a sheet array try:

Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long
Dim varSheets As Variant
Dim varOut As Variant
Dim i As Integer
Application.ScreenUpdating = False

varSheets = Array("Sheet1", "Sheet2", "Sheet3")
lastRowDest = 1
For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
varOut = .Range("A1:A" & lastRow)
Sheets("Sheet4").Cells(lastRowDest, 1) _
.Resize(rowsize:=lastRow) = varOut
lastRowDest = Sheets("Sheet4").Range("A" & Rows.Count) _
.End(xlUp).Row + 1
End With
Next

Application.ScreenUpdating = True
MsgBox "Done!"

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2