Copy a range and have it transposed and inserted into another file with VBA

Posted by Chris on November 11, 2001 1:35 AM

I'm trying to copy a range of cells from one xls to another. But I need it transposed in the new xls. I called the transpose function on the range, and then set it to the range in the new xls. I only get the first cell value of the range and not the whole thing. Any suggestions?

Posted by Jack on November 11, 2001 2:23 AM

Could i ask you to post up your VBA Scrpt or Formula your using: Must it be VBA? Are you able yo use VBA IE Add acode to a button OK?

Posted by Juan Pablo on November 11, 2001 10:12 AM

This worked for me.

Sub TransposeIt()
Dim ROr As Range
Set ROr = Sheets("Hoja2").Range("A1:C5") '5x3 Table {1,2,3;4,5,6;7,8,9;10,11,12;13,14,15}
Sheets("Hoja3").Select
Sheets("Hoja3").Range(Cells(1, 1), Cells(ROr.Columns.Count, ROr.Rows.Count)).FormulaArray = "=TRANSPOSE(Hoja2!" & ROr.Address & ")"
End Sub

The thing is that the TRANSPOSE function must be entered as an Array function. This should take care of that.

Juan Pablo