View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
RyanH RyanH is offline
external usenet poster
 
Posts: 586
Default 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