There are two forms of pastespecial. The one that has transpose as argument
is a method of a range object. This works (as long as j is less than 256)

Sub SelectWithoutBlanks()

Dim j As Integer
Do
j = j + 1
'Debug.Print j
If ActiveSheet.Cells(j, 1).Value = "" Then Exit Do
Loop

ActiveSheet.Range(Cells(1, 1), Cells(j - 1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(j - 1, 1)).Copy
MsgBox ("Range Selected")

ActiveSheet.Cells(1, 2).PasteSpecial Transpose:=True
End Sub

--
Regards,
Tom Ogilvy

"Willow" wrote in message
...

Hi all

I am new to vba, and am having problems transposing data. This is
supposed to select a column of data ( until there is a blank cell),
copy and then attempt to paste / transpose into cell B2 on the same
sheet. All works except for the paste line.Please would someone
explain why it does not work?

Thanks


Sub SelectWithoutBlanks()

Dim j As Integer
Do
j = j + 1
'Debug.Print j
If ActiveSheet.Cells(j, 1).Value = "" Then Exit Do
Loop

ActiveSheet.Range(Cells(1, 1), Cells(j - 1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(j - 1, 1)).Copy
MsgBox ("Range Selected")

ActiveSheet.Cells(1, 2).Select
ActiveSheet.PasteSpecial Transpose:=True
End Sub


--
Willow
------------------------------------------------------------------------
Willow's Profile:
http://www.excelforum.com/member.php...o&userid=16070
View this thread: http://www.excelforum.com/showthread...hreadid=275233