First, I wouldn't use Row as a variable name. I wouldn't use "As Integer" or
"as Byte" either.

And since you're within a "With/End With" block, you can drop some of those
Worksheets("Sheet1") references.

Wait! Wait!

Those references are probably typos. You want the info to go to Sheet2!

And depending on your data, your code could be having trouble with the
..resize(cols) expression.

If there's 1 entry or 0 entries in that row, then it would cause a 1004 error.

But that's a guess, since you didn't say what line caused the error.

I have no idea if this does what you want/expect, but it did compile and run for me:

Option Explicit
Sub Copy_transpose()
Dim myRow As Long
Dim Cols As Long
Dim nRow As Long
Application.ScreenUpdating = False
nRow = 1

With Worksheets("Sheet1")
For myRow = 1 To 668
Cols = Application.CountA(.Range("a" & myRow).EntireRow) - 1

If Cols < 1 Then
'do nothing
Else
Worksheets("Sheet2").Rows(nRow).Resize(Cols).Value _
= .Range("a" & myRow).Value

Worksheets("Sheet2").Range("b" & nRow).Resize(Cols).Value _
= Application.Transpose(.Range("b" & myRow) _
.Resize(, Cols).Value)

nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
End If

Next myRow
End With
End Sub




On 05/18/2010 14:24, sebastico wrote:
Hello
I have this code taht when I run displays Run-timeerror 1004:
Application-defined or object-defined error.

Could you suggest me how to fix it?

Thanks in advance

Sub Copy_transpose()
Dim Row As Integer, Cols As Byte, nRow As Integer
Application.ScreenUpdating = False
nRow = 1

With Worksheets("Sheet1")
For Row = 1 To 668
Cols = Application.CountA(.Range("a"& Row).EntireRow) - 1
Worksheets("Sheet1").Range("a"& nRow).Resize(Cols).Value = .Range("a"&
Row).Value
Worksheets("Sheet1").Range("b"& nRow).Resize(Cols).Value =
Application.Transpose(.Range("b"& Row).Resize(, Cols).Value)
nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
Next
End With
End Sub