I just thought of a good improvement to make the code compatabble witth 2003
and 2007

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/17/2007
'

'
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Sheets(3)
ActiveSheet.Name = "Sheet4"

LastRow = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp).Row
If LastRow (Rows.Count - 1) Then LastRow = Rows.Count - 1
Sheets("Sheet2").Activate
Set copyrange = Range(Cells(1, "A"), Cells(LastRow, "A"))

copyrange.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
[B1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks :=False, Transpose:=True
End Sub



"Joel" wrote:

when you are usingg 2003 there is a limit of the number of columns to 256.
when you transpose 65535 row into columns you get an error. This problem was
noted on the original request when Mr. Matt said the data can be "any Length".

"Incidental" wrote:

Hi Mr. Matt

The code below would be one way of doing it

Option Explicit
Dim LstRow As Integer
Dim WkSh As Worksheet
Dim MyRng As Range

Private Sub CommandButton1_Click()

Sheets(2).Activate

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

Sheets("Sheet3").Copy After:=Sheets(3)

ActiveSheet.Name = "Sheet4"

MyRng.Copy

[B1].PasteSpecial Transpose:=True

End Sub

Hope this helps

S