On Tuesday, April 28, 2015 at 9:38:33 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 28 Apr 2015 09:28:11 -0700 (PDT) schrieb L. Howard:

The code needs to assume that CF and VBA are blank sheets to start, and must be created by the code from the huge sheet 1 layout.

sorry, my bad.

To transpose the data from sheet1 to sheet4 use:

Sub Transpose()
Dim varData As Variant, varHeader As Variant
Dim varOut() As Variant
Dim i As Long, j As Long, m As Long, n As Long
Dim LRow As Long, LCol As Long, myCnt As Long

varHeader = Array("Position", "Name", "Qualification")
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
LCol = .Cells(2, Columns.Count).End(xlToLeft).Column
For i = 4 To LCol Step 2
myCnt = myCnt + Application.CountA(.Range(.Cells(3, i),
.Cells(LRow, i)))
Next

varData = .Range(.Cells(3, 1), .Cells(LRow, LCol))
ReDim varOut(myCnt - 1, 2)
For i = LBound(varData) To UBound(varData)
m = 0
varOut(n, m) = varData(i, 1)
m = m + 1
varOut(n, m) = varData(i, 3)
m = m + 1
For j = 4 To LCol Step 2
If Len(varData(i, j)) 0 Then
varOut(n, m) = .Cells(1, j)
n = n + 1
End If
Next
Next
End With
Sheets("Sheet4").Range("A1").Resize(, 3) = varHeader
Sheets("Sheet4").Range("A2").Resize(myCnt, 3) = varOut

Sheets("Sheet4").Range("A1:C1").Font.Bold = True
Sheets("Sheet4").Range("A1:C1").HorizontalAlignmen t = xlCenter
End Sub


Regards
Claus B.
--


And if I call code Format() from Transpose code -- BINGO!

Nice job!!

Thanks Claus.

Howard