Recognize first emtpy Cell in column

[JohnDK]

How can i code to copy range (which is not always going to be the same) from one book.sheet to another. The code would have to look for the first emtpy cell in
the latter book, and thenn paste.
Currently i select all cells (which i dont want, i need to select all data cells without header row) and paste in new book after selecting range. Range is dynamic , so i have to code to look for first emtpy cell where i need to paste.

any ideas.
Thanx

sub
ActiveSheet.Cells.Select
ActiveSheet.Cells.EntireColumn.AutoFit
ActiveSheet.Range("c1").Sort _
Key1:=ActiveSheet.Columns("c"), _
Header:=xlYes
ActiveSheet.Cells.Select
Selection.Copy

Workbooks.Open "C:\Documents and Settings\User\Desktop\ALLTIE.xls", , False
AppActivate "Microsoft Excel"ActiveSheet.Cells.Select
ActiveSheet.Paste
end sub

[dok112]

what column is the empty cell going to be in? Or is this a variable that is unknown? Because you could do a loop to look for a empty cell and then have it copy the data over? Is this what you are looking for?

[Gary Keramidas]

if it's also the lastrow in the column, use this

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

this assume it's sheet1 and column A, just adjust to your needs

then paste to range("A" & lastrow)

--


Gary


"JohnDK" <JohnDK.1y05sm_1131158402.1048@excelforum-nospam.com> wrote in
message news:JohnDK.1y05sm_1131158402.1048@excelforum-nospam.com...[color=blue]
>
> How can i code to copy range (which is not always going to be the same)
> from one book.sheet to another. The code would have to look for the
> first emtpy cell in
> the latter book, and thenn paste.
> Currently i select all cells (which i dont want, i need to select all
> data cells without header row) and paste in new book after selecting
> range. Range is dynamic , so i have to code to look for first emtpy
> cell where i need to paste.
>
> any ideas.
> Thanx
>
> [COLOR=RoyalBlue]ActiveSheet.Cells.Select
> ActiveSheet.Cells.EntireColumn.AutoFit
> ActiveSheet.Range("c1").Sort _
> Key1:=ActiveSheet.Columns("c"), _
> Header:=xlYes
> ActiveSheet.Cells.Select
> Selection.Copy
>
> Workbooks.Open "C:\Documents and Settings\User\Desktop\ALLTIE.xls", ,
> False
> AppActivate "Microsoft Excel"ActiveSheet.Cells.Select
> ActiveSheet.Paste
>
>
> --
> JohnDK
> ------------------------------------------------------------------------
> JohnDK's Profile:
> http://www.excelforum.com/member.php...fo&userid=7184
> View this thread: http://www.excelforum.com/showthread...hreadid=482406
>

[JohnDK]

it will be the 1st empty cell in column C, but then have to paste it in corresponding cell column A (A contains multiple empty cells).

Similarly how do i copy a dynamic range for the origin sheet without header row?

Thanks

[chijanzen]

try:

Dim rng As Range, wbk As Workbook
ActiveSheet.UsedRange.EntireColumn.AutoFit
ActiveSheet.Range("c1").Sort _
Key1:=ActiveSheet.Columns("c"), _
Header:=xlYes
Set rng = ActiveSheet.UsedRange
Set wbk = Workbooks.Open("C:\Documents and
Settings\User\Desktop\ALLTIE.xls", , False)
rng.Copy wbk.ActiveSheet.Cells
Set rng = Nothing

--
http://www.vba.com.tw/plog/


"JohnDK" wrote:
[color=blue]
>
> How can i code to copy range (which is not always going to be the same)
> from one book.sheet to another. The code would have to look for the
> first emtpy cell in
> the latter book, and thenn paste.
> Currently i select all cells (which i dont want, i need to select all
> data cells without header row) and paste in new book after selecting
> range. Range is dynamic , so i have to code to look for first emtpy
> cell where i need to paste.
>
> any ideas.
> Thanx
>
> [COLOR=RoyalBlue]ActiveSheet.Cells.Select
> ActiveSheet.Cells.EntireColumn.AutoFit
> ActiveSheet.Range("c1").Sort _
> Key1:=ActiveSheet.Columns("c"), _
> Header:=xlYes
> ActiveSheet.Cells.Select
> Selection.Copy
>
> Workbooks.Open "C:\Documents and Settings\User\Desktop\ALLTIE.xls", ,
> False
> AppActivate "Microsoft Excel"ActiveSheet.Cells.Select
> ActiveSheet.Paste
>
>
> --
> JohnDK
> ------------------------------------------------------------------------
> JohnDK's Profile: http://www.excelforum.com/member.php...fo&userid=7184
> View this thread: http://www.excelforum.com/showthread...hreadid=482406
>
>

[JohnDK]

Thanks all for the suggestions!!! WOW

chijanzen - Thx.
activesheet.usedrange works, however is there a way i can deselect the header row in selection.copy because it appends in another sheet to excisting data.

Thanks again