copy & paste macro
 

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

copy & paste macro
 

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

copy & paste macro
 

I asume the code is not in EV.xls but in the other workbook

You can find more info here (see the last example)
http://www.rondebruin.nl/copy1.htm

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


"Arul" wrote in message ...
Ron,
The macro runs off of the EV.xls file. The test.xls file is opened via the
macro and the macro performs certain calculations on it. Once done, I'm
trying to copy & paste that data into EV.xls. Does your code assume
otherwise? I'm sorry if there was any confusion in my descr.

"Ron de Bruin" wrote:

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

copy & paste macro
 

Thanks Ron. Not a big VBA wiz, however, managed to customize it.

"Ron de Bruin" wrote:

I asume the code is not in EV.xls but in the other workbook

You can find more info here (see the last example)
http://www.rondebruin.nl/copy1.htm

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


"Arul" wrote in message ...
Ron,
The macro runs off of the EV.xls file. The test.xls file is opened via the
macro and the macro performs certain calculations on it. Once done, I'm
trying to copy & paste that data into EV.xls. Does your code assume
otherwise? I'm sorry if there was any confusion in my descr.

"Ron de Bruin" wrote:

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

copy & paste macro
 

Hi Arul,

Another thing to watch out for is assuming that the rows and columns will
"never" change in Excel. Excel now has 65536 rows which requires a long
integer. The sizes previous that only required an integer variable
for 16384 rows then 32768 rows. So don't tie yourself down to
integer variable use LONG and don't tie yourself down with fixed numbers.

Tom Ogilvy would code that part without a specific row number something like
Cells(Rows.Count, ActiveCell.Column).End(xlUp)
or for coloumn A
Cells(Rows.Count,"A").End(xlUp)

Of course this is really a method of trying to get around the fact that
Excel does not maintain a correct used range.

A couple of my favorite macros using Tom's coding can be seen in my
http://www.mvps.org/dmcritchie/excel/toolbars.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Arul" wrote in message ...
Thanks Ron. Not a big VBA wiz, however, managed to customize it.