Solved: Copy column and paste into rows

[anthony20069]

Hi guys,

I have a column B4:B(end of row) which contains data....

What i am trying to do is copy that data from sheet1 and paste into sheet2 in

G5:(x)5 = where x is how many rows from above there are

Any help would be appreciated :)

cheers

[jolivanes]

I took the liberty of assuming that "B4:B(end of row)" should be end of column.
Is this what you have in mind? Try it on a copy of your workbook.

Sub Transpose_To_Sheet2()
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 2).End(xlUp).Row
Range("B4:B" & lr).Copy
Sheets("Sheet2").Range("G5").PasteSpecial Paste:=xlValues, Transpose:=True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[shrivallabha]

Maybe this:

Public Sub TransposeData()
'Clean up data on sheet2
With Sheets("Sheet2")
If Not .Range("G5").End(xlToRight).Column = Columns.Count Then
.Range("G5", .Range("G5").End(xlToRight)).ClearContents
End If
End With
'Check if we have data on sheet1 and then only copy
If Range("B" & Rows.Count).End(xlUp).Row >= 4 Then
Range("B4", Range("B" & Rows.Count).End(xlUp)).Copy
Sheets("Sheet2").Range("G5").PasteSpecial xlPasteAll, Transpose:=True
Application.CutCopyMode = False
End If
End Sub

[snb]

sub M_snb()
with sheets("sheet1").cells(4,2).resize(sheets("sheet1").cells(rows.count,2).end(xlup).row-3)
sheets("sheet2").cells(5,7).resize(,.rows.count)=application.transpose(.value)
end with
end sub

[anthony20069]

I took the liberty of assuming that "B4:B(end of row)" should be end of column.
Is this what you have in mind? Try it on a copy of your workbook.

Sub Transpose_To_Sheet2()
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 2).End(xlUp).Row
Range("B4:B" & lr).Copy
Sheets("Sheet2").Range("G5").PasteSpecial Paste:=xlValues, Transpose:=True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Works like a charm :)

Just a couple questions on this.

What does Transpose:=True do?

[jolivanes]

Transpose: Switches from Columns to Rows or from Rows to Columns.
If you type "Transpose" (without quotations) in Help you'll get all kinds of information.

Good luck

John

[anthony20069]

nice-one, cheers john!