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