Hi Neil,

Am Mon, 14 Jul 2014 11:47:16 +0100 schrieb Neil Robinson:

Name: Name 1
Age: Age 1
email address: email address 1
date of registration: date of registration 1
subscription expiry: subscription expiry 1
outstanding fee: Oustanding fee 1

Name: Name 2
Age: Age 2
email address: email address 2
date of registration: date of registration 2
subscription expiry: subscription expiry 2
outstanding fee: Oustanding fee 2

your data in Sheet1 column A. Then the following code will write your
data with the expected formatting to Sheet2:

Sub Transpose()
Dim LRow As Long, i As Long, n As Long
Dim arrHeaders As Variant
Dim myRng As Range, rngC As Range


arrHeaders = Array("Name", "Age", "Email Address", "Date of
registration", _
"Subscription expiry", "Outstanding fee")

Sheets("Sheet2").Range("A1:F1") = arrHeaders
n = 2
i = 1

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
n = IIf(i Mod 7 = 0, n + 1, n)
If Len(rngC) 0 Then
Sheets("Sheet2").Cells(n, i) = _
Mid(.Cells(rngC.Row, 1), _
InStr(.Cells(rngC.Row, 1), ":") + 2, 99)
End If
i = IIf(i Mod 7 = 0, 1, i + 1)
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional