Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF THEN copy & paste macro | Excel Worksheet Functions | |||
HELP with macro for copy and paste | Excel Discussion (Misc queries) | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
Copy and Paste Macro Help please.... | Excel Programming | |||
MACRO TO COPY AND PASTE! | Excel Programming |