Excel rows to columns - certificates

[stevejpearson]

Dear all,

I need to print 100's of certificates and want to run a mail merge. However, I currently have a column of names and then the certificates the names are receiving appear in the same row as the name e.g. row 2 has won awards for progress in art and DT and in achievement for Maths and French. To run the mail merge I really need the names to be repeated on different rows with the name of the award appearing in the next column e.g.

Name 1 Art Progress
Name 1 DT Progress
Name 1 Maths Achievement
Name 1 French Achievement

Can anyone help? Is there a quick way to do this!? You can view the file here:

https://docs.google.com/open?id=0B2kA5LQXm97jc3Ftc1pFaFc4SkU

Thanks

Steve

 

[Tinbendr]

This adds a new sheet with the results.

Sub NewGoalSheet()
Dim WS As Worksheet
Dim MyArray As Variant
Dim NewArray() As Variant
Dim LastRow As Long
Dim CTR As Long
Dim A As Long
Dim B As Long

Set WS = ActiveWorkbook.Worksheets("P& A nominations")
With WS
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

MyArray = .Range("B2:AK" & LastRow)
ReDim NewArray(1 To LastRow * 4, 1 To 2)
CTR = 1
For A = 1 To UBound(MyArray)
    For B = 7 To UBound(MyArray, 2)
        If MyArray(A, B) <> "" Then
            NewArray(CTR, 1) = MyArray(A, 1)
            NewArray(CTR, 2) = MyArray(A, B)
            CTR = CTR + 1
        End If
    Next
Next

Set WS = Worksheets.Add
WS.Cells.Resize(CTR, 2).Value = NewArray
End With

End Sub

 

[stevejpearson]

Thank you - this is amazing. I so need to learn VBA.

Steve