TRANSPOSE grid?

[Guest]

Hi,

I have a worksheet (lets call it W1) which is nearly full with data. I wish
to put this data into a second worksheet (W2) but with all the data
transposed. Here's the tricky bit. I want the data in W2 to update if I alter
anything in W1. Is it possible to transpose all of the data at once.....or do
I have to do row by row?

Thanks in advance.

 

[Roger Govier]

Hi

If your sheet is nearly full, then you will not be able to Transpose.
Excel (currently) has a limit of 256 columns and 65536 rows.
Once there are more than 256 rows used in your sheet, Transpose cannot
work.

 

[Clivey_UK]

One way of doing this is to record yourself doing it once as a macro
Note that you can only have 256 columns, so you can't transpose >25
rows.
Select the whole table in W1, copy it, go to W2, Edit/Past
Special/Transpose. The macro will look something like this
Range("A1:Z250").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=True

Then you'd need to run the macro each time you want to update W2.
Clive