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