Hi David,

Am Sun, 31 Mar 2013 09:34:56 +0000 schrieb davidshe:

I have just started learning VBA and a friend designed the following
macro.
The macro takes information from Cell A2 to F2 and creates a new record
in a single column. That is the is transposed from horizontal across
columns to a single column, with line break at each record.

[Code snippet]

Select, selection and Activate is not necessary if your referencies are
explicit. I prefer a solution without formulas because formulas will be
new calculated.
My solution copies the range in each row in sheet1 and paste it in
column A of sheet2:

Sub UpdateData()
Dim lastColumn As String
Dim destinationArray As String 'string = text
Dim numOfColumns As Integer
Dim i As Long, lastRow As Long
Dim destinationStart As Long

Application.ScreenUpdating = False
'modify the sheet name
With Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
numOfColumns = .Cells(2, .Columns.Count).End(xlToLeft).Column

destinationStart = 1

For i = 2 To lastRow
.Range(.Cells(i, 1), .Cells(i, numOfColumns)).Copy
'modify the sheet name
Sheets("Sheet2").Cells(destinationStart, 1) _
.PasteSpecial xlPasteAll, Transpose:=True
destinationStart = destinationStart + numOfColumns
Next
End With
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub


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