Hi Arul

This example copies a range without the header row on sheet1 to the first
free row on sheet2

Sub test()
Dim tbl As Range
Set tbl = Sheet1.Range("A1").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Copy Sheet2.Range("A65000").End(xlUp).Offset(1, 0)
End Sub
As you want to copy between workbooks, you have to refer to it as well
Regards,

Jean-Yves

"Arul" wrote in message
...
I have two excel files text.xls and EV.xls, both of which are open.
I am trying to copy data from text.xls without the header row (currently
the
active file) and paste it after the last row in EV.xls...using a macro.

Both files have identical number of columns.

Thanks

Hi

Be careful with this

Range("A65000").End(xlUp).
Maybe there is data in the other columns and not in the A column.
You will overwrite some rows then.

Maybe you can use this

It will open the database workbook if it is not open for you.
Note: I use in both files a sheet named Sheet1

Copy the macro and the two functions in a normal module.

Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("EV.xls") Then
Set destWB = Workbooks("EV.xls")
Else
Set destWB = Workbooks.Open("C:\EV.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1

With ThisWorkbook.Sheets("Sheet1").UsedRange
Set sourceRange = .Offset(1, 0).Resize(.Rows.Count - 1, _
.Columns.Count)
End With

Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jean-Yves" wrote in message ...
Hi Arul

This example copies a range without the header row on sheet1 to the first
free row on sheet2

Sub test()
Dim tbl As Range
Set tbl = Sheet1.Range("A1").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Copy Sheet2.Range("A65000").End(xlUp).Offset(1, 0)
End Sub
As you want to copy between workbooks, you have to refer to it as well
Regards,

Jean-Yves

"Arul" wrote in message
...
I have two excel files text.xls and EV.xls, both of which are open.
I am trying to copy data from text.xls without the header row (currently
the
active file) and paste it after the last row in EV.xls...using a macro.

Both files have identical number of columns.

Thanks