Copy data from one column across one row....

G

Guest

Hey guys,

I would like to take one column of data and copy it across one row.,
so, I want the data to go left to right across the spreadsheet instead of
top to bottom.

If you have a suggestion to solve this, macro or whatever, could you please
email it
to me at:

[email protected]

thanks,
kevin rea
 
N

nilizandr

hi,
try edit --> paste special --> click on "transpose" when you paste.
pasting formulas could become a headache, depending on how complicate
the operation is. in these cases i always resort to pasting values
but if others have suggestions about that, i'd be glad to hear them
 
G

Gord Dibben

nilizandr

Dealing with formulas..........

Manually, Edit>Replace the = sign with something unique like %%%

Do your copy and transpose then reverse your replace.

You could use VBA to transpose the formulas.

Picked this code up on the 'net. Can't remember who to give attribution to
but it works.

Sub Transpose_Formulas()
Dim sRange As Range, dCell As Range
Dim sCell As Range, i As Integer, j As Integer
Dim Str As String

'get input ranges. default box is filled by use of text
'variable set to the selected address
Str = Selection.Address(False, False)
Application.ScreenUpdating = True
On Error Resume Next
Set sRange = Application.InputBox(prompt:= _
"Select the range of cells to be transposed." & Chr(10) & Chr(10) _
& "If cells do not have Formulas, Sub will end!.", Type:=8, _
default:=Str)
If Not sRange.HasFormula Then
MsgBox "Cells do not contain formulas"
End
Else
If sRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
"Select the top left cell of the output location.", _
Type:=8)
If dCell Is Nothing Then End
On Error GoTo 0
'set single cell references for use in the next step
Set sCell = sRange.Cells(1, 1)
Set dCell = dCell.Cells(1, 1)

'loop through all cells, working backward to the top left cell
For i = sRange.Rows.Count - 1 To 0 Step -1
For j = sRange.Columns.Count - 1 To 0 Step -1
If i > 0 Or j > 0 Then
'do this for all but the first cell
sCell.Offset(i, j).Cut _
Destination:=dCell.Offset(j, i)

Else
'do top corner last. Otherwise references are changed
sCell.Cut Destination:=dCell
End If
Next j
Next i
End If
End If

End Sub

Gord Dibben Excel MVP
 
Top