finding the next empty column
Peter, I cleaned up the code to make if more readable. This will do what
you are needing.
Option Explicit
Sub CopyRange()
Dim lngLastRow As Long
Dim lngLastColumn As Long
' find last row in Col.A on Sheet1
lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' find last column in Row 1 on Sheet2
lngLastColumn = Sheets("Sheet2").Cells(1,
Columns.Count).End(xlToLeft).Column
' copy range on Sheet1 to Sheet2
Sheets("Sheet1").Range("A1:A" & lngLastRow).Copy _
Destination:=Sheets("Sheet2").Cells(1, lngLastColumn + 1)
End Sub
I hope this helps! If so, please click "Yes" below.
--
Cheers,
Ryan
"Peter" wrote:
i am new to vba and am keen to learn from others with more experience.
this came from this ms discussion groups. it copies a range in col 1 of
sheet 1 to col 1 of sheet2
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy Sheets("Sheet2").Range("A1")
End With
End Sub
i am happy to use this but i would like it to be a bit more sophisticated in
choosing where in sheet 2 it pastes.
i would like the destination in sheet 2 to be "..in the colum immediately to
the right of the last colum that has data in it".
could someone pls start me off with a bit of code pls.
thanking you in advance.
--
Peter
|