Private Sub transpose()
Worksheets("Sheet1").range("a1:a21").Copy
Worksheets("Sheet2").range("a1").PasteSpecial _
Paste:=xlPasteAll, Transpose:=True


End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"paste special, macro, transpose"
. com wrote in message
...
Thank you, but for some reason "record macro" was unavailable - it was
light
grey. I thought it wasn't allowed on the office computer I'm working on
(no
idea for what reason when VBA is allowed :S ).
After closing and restarting the excel sheet I was allowed to record
though.
So thanks for your help - it made me try again :D.

"Don Guillett" wrote:


Try recording to see what you did wrong

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"paste special, macro, transpose" <paste special, macro,
wrote in message
...
He hi,

It's been a while since I used VBA so the following might contain a
very
obvious error. I'd be very grateful if somebody could tell me what I do
wrong
in my code:

Private Sub transpose()
Worksheets("Sheet1").Copy
Worksheets("Sheet2").PasteSpecial (transpose)
End Sub

I wrote this to make sure whole of sheet 1 is pasted in a transposed
manner
(thus columns become rows and other way around) into sheet 2. For in
sheet
1
I enter new data, but to make sorting (I've only figured out how to do
that
by column) easier it has to be transposed before I do calculations with
it.
Doing this by hand means copying all of sheet 1 and use "paste special"
and
tick "transpose" to paste it in sheet 2.

I'm not sure if it's right to write a private sub for this... or
whether
this code is the right one... Or wether I should write a loop like
"copy
row
1 and paste in column A"

Greetz, Robin