Hi Howard,

Am Mon, 18 Nov 2013 22:33:03 -0800 (PST) schrieb Howard:

In workbook, I have six sheets. Sheets 1, 2 & 3 have data in column A with varied number of rows.

The code here copies each of those columns to sheet 4, Column A. BUT does it twice. Sheet data 1, 2, 3 listed in sheet 4 followed by an identical list right below the first.

Once I get the double copy solved, I intend to use an array with the elements as the sheets I want to copy from. There is a sheet 5 & 6 but they are blank now. Still I would prefer to avoid even looking at them unless they were included in the array.

try:

Sub ThreeColumnsToOne()

Dim lastRow As Long, lastRowDest As Long
Dim varOut As Variant
Dim i As Integer

Application.ScreenUpdating = False

lastRowDest = 1

For i = 1 To 3
With Sheets(i)
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varOut = .Range("A1:A" & lastRow)
Sheets(4).Cells(Rows.Count, lastRowDest).End(xlUp) _
.Offset(1, 0).Resize(rowsize:=lastRow) = varOut
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