finding the next empty column
 

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

finding the next empty column
 

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Sht2LastRow = _
Sheets("Sheet2").Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Range("A" & Sht2LastRow)
End With
End Sub

"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

finding the next empty column
 

hi
try this
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy _
Sheets("Sheet2").Range("A1"). _
End(xlToRight).Offset(0, 1)
End With
End Sub

all that was added was...End(xlToRight).Offset(0,1)
add to the end of the destination.

works in 03

regards
FSt1


"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

finding the next empty column
 

Small change

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Sht2LastCol = _
Sheets("Sheet2").Cells(1,Columns.Count).End(xltole ft).Row
Sh2NewCol = Sht2LastCol + 1
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Cells(1,Sh2NewCol)
End With
End Sub


"Joel" wrote:

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Sht2LastRow = _
Sheets("Sheet2").Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Range("A" & Sht2LastRow)
End With
End Sub

"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

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

finding the next empty column
 

xlToRight will not work. It will go to the end of the spreadsheet not to the
last column with data in it. In this case xlToLeft is needed.

--
Cheers,
Ryan


"FSt1" wrote:

hi
try this
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy _
Sheets("Sheet2").Range("A1"). _
End(xlToRight).Offset(0, 1)
End With
End Sub

all that was added was...End(xlToRight).Offset(0,1)
add to the end of the destination.

works in 03

regards
FSt1


"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

finding the next empty column
 

ryan,
many thanks mate!
this works fine. it also can tolerate a blank column when there is a column
to the right of it. ie it skips this col and finds the last column with
somehting in it. this is what i wanted so thankyou for your help.
peter
--
Peter


"RyanH" wrote:

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

finding the next empty column
 

hey fst1,
thankyou for helping me. this worked very well. it finds the first empty col
as requested. however, in cases where there is an empty col among others
that have data, it wont skip an emplty column and still find the last one to
the righ of it with somehting in it. i actually didnt specify i wanted this.
i will keep this code to cover the cases where i dont want to skip an empty
column.
thankyou again for all your help.
--
Peter


"FSt1" wrote:

hi
try this
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy _
Sheets("Sheet2").Range("A1"). _
End(xlToRight).Offset(0, 1)
End With
End Sub

all that was added was...End(xlToRight).Offset(0,1)
add to the end of the destination.

works in 03

regards
FSt1


"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

finding the next empty column
 

hi joel,
the first version of your suggestion was good but it put the column after
the last full cell in the first col. its not quite what i wanted but its
going to come in handy in the future for these cases.
many many thanks for helping me.

--
Peter


"Joel" wrote:

Small change

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Sht2LastCol = _
Sheets("Sheet2").Cells(1,Columns.Count).End(xltole ft).Row
Sh2NewCol = Sht2LastCol + 1
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Cells(1,Sh2NewCol)
End With
End Sub


"Joel" wrote:

Sub test()
With Sheets("Sheet1")
Sht1LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Sht2LastRow = _
Sheets("Sheet2").Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & Sht1LastRow).Copy _
Destination:=Sheets("Sheet2").Range("A" & Sht2LastRow)
End With
End Sub

"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

finding the next empty column
 

hi
i assumed that you had a solid block of data becasue you said......
"..in the colum immediately to the right of the last colum that has data in it".
modify the code a tad...change the destintion......
Sheets("Sheet2").Range("IV1"). _
End(xlToLeft).Offset(0, 1)

that should fix it.
Regards
FSt1

"Peter" wrote:

hey fst1,
thankyou for helping me. this worked very well. it finds the first empty col
as requested. however, in cases where there is an empty col among others
that have data, it wont skip an emplty column and still find the last one to
the righ of it with somehting in it. i actually didnt specify i wanted this.
i will keep this code to cover the cases where i dont want to skip an empty
column.
thankyou again for all your help.
--
Peter


"FSt1" wrote:

hi
try this
Sub test()
With Sheets("Sheet1")
.Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy _
Sheets("Sheet2").Range("A1"). _
End(xlToRight).Offset(0, 1)
End With
End Sub

all that was added was...End(xlToRight).Offset(0,1)
add to the end of the destination.

works in 03

regards
FSt1


"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