The following code should fit your needs.
Paste it into a module, then run macro transpose.

HTH
--
AP

'--------------------------------------------------
Option Explicit

Sub transpose()
Const strPlease As String = "Please"
Dim rHead As Range
Dim rVal As Range
Dim destWS As Worksheet
Dim iRownum As Long

With Worksheets("Sheet1")
' Find Column header containing "Please"
Set rHead = .Rows(3).Find( _
what:=strPlease, _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByColumns)
If rHead Is Nothing Then
MsgBox "No header with please"
Exit Sub
End If
End With
' Create new worksheet
Set destWS = Worksheets.Add( _
after:=Worksheets(Worksheets.Count))
destWS.Name = "Result"
iRownum = 1
' Loop thru columns in source worksheet,
' starting with column next to "Please"
Set rHead = rHead.Offset(0, 1)
Do While rHead.Value < ""
' loop thru rows of results in source worksheet
Set rVal = rHead.Offset(1, 0)
Do While rVal.Value < ""
' Put values in dest WS
destWS.Cells(iRownum, 1).Value = strPlease
destWS.Cells(iRownum, 2).Value = rHead.Value
destWS.Cells(iRownum, 3) = rVal.Value
' Skip to next row in source WS
Set rVal = rVal.Offset(1, 0)
' Skip to next row in dest WS
iRownum = iRownum + 1
Loop
' skip to next column
Set rHead = rHead.Offset(0, 1)
Loop

End Sub
'-------------------------------------------------

"ebachenh" a écrit
dans le message de
...

Hello:

I am pretty new to VB with Excel, and I'm hoping someone can help with
write some code for something which is probably pretty easy for some of
you.

I have a series of Excel files with cell values in a certain layout,
and I really just want to create a macro that will "transpose" the
values.

Its hard to descibe the layout, but the "headers" are in row 3, and the
values follow in rows below (each file has different # of rows). What I
need to do is extract a certain block of question headers, starting with
the one column header that includes the text "Please", through the last
one. To the right of each question in the block are names, with scores
to that question in rows below. See below for the sample layout:


A B C D E
3 Q1 Q2 Q3: Please John Jane
4 blah blah 5 4
5 blah blah 4 3
6 blah blah 4 4
</table</HTML
What I would like to do is move them to a new sheet with a layout
like:

A B C
1 Please John 5
2 Please John 4
3 Please John 4
4 Please Jane 4
5 Please Jane 3
6 Please Jane 4 ......etc.

I have so much data that it takes forever to manually do this, so if
anyone can offer some coding help, it would be so very much appreciated
by this newbie.

Thanks,
EBox


--
ebachenh
------------------------------------------------------------------------
ebachenh's Profile:
http://www.excelforum.com/member.php...o&userid=32370
View this thread: http://www.excelforum.com/showthread...hreadid=521301