PasteSpeacial - transpose

[Willow]

Hi all

I am new to vba, and am having problems transposing data. This i
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 sam
sheet. All works except for the paste line.Please would someon
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 Su

 

[Tom Ogilvy]

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