Dim myRow As Range
Dim iSht As Integer

iSht = 1
For Each myRow In Workbooks("A.xls"). _
Worksheets("Sheet1").Range("A2:D11").Rows
myRow.Copy
Workbooks("B.xls").Worksheets("Sheet" & iSht). _
Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True
iSht = iSht + 1
Next myRow



Perhaps, just this instead - depends on your naming convention.

Workbooks("B.xls").Worksheets(iSht). _
Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True

HTH,
Bernie
MS Excel MVP


"L. Howard Kittle" wrote in message
. ..
Hello Excel Experts and Users,

Excel 2002.

Workbook A has a list, A2 to A11, four columns wide.
Want to copy row 2 of A to Workbook B, sheet 1, "to a cell" and transpose
...copy row 3 of A to Workbook B, sheet 2 "to a cell" and transpose
...copy row 4 of A to Workbook B, sheet 3 "to a cell" and transpose
...etc until all ten rows have been copied and transposed to B.

Where "to a cell" will be an .end(xltoleft).offset(0,1)

I found this snippet in Google, Tom O. I believe, and tried to adapt it but it ain't happenin'.

Dim WS as Worksheet
Set WS = Worksheets(1)
Do Until WS.Name = ("Sheet3)
'do stuff to the worksheets
Set WS = WS.next
Loop

I can get the first row of A to the first sheet in B but then fall on my face in the code.
The code needs to loop thru the 10 rows of book A and thru the 10 sheets of book B.

Thanks all,
Regards,
Howard