Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
Hi
Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
that is just copying 1 cell. I don't see how that could take 12 seconds.
do you have pagebreaks visible? If so, try turning them off. -- Regards, Tom Ogilvy "Geoff" wrote: Hi Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
Hi
With respect, the code copies all rows of columns A to J (last column) in the data sample I am using. I find there is little point in finding the last row for this purpose - there is no rduction in time. There are no page breaks. Geoff "Tom Ogilvy" wrote: that is just copying 1 cell. I don't see how that could take 12 seconds. do you have pagebreaks visible? If so, try turning them off. -- Regards, Tom Ogilvy "Geoff" wrote: Hi Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
OK - I stand corrected. Misread what you were returning in the split line.
the only other alternative would be to do something akin to a pastespecial xlvalues -- Regards, Tom Ogilvy "Geoff" wrote: Hi With respect, the code copies all rows of columns A to J (last column) in the data sample I am using. I find there is little point in finding the last row for this purpose - there is no rduction in time. There are no page breaks. Geoff "Tom Ogilvy" wrote: that is just copying 1 cell. I don't see how that could take 12 seconds. do you have pagebreaks visible? If so, try turning them off. -- Regards, Tom Ogilvy "Geoff" wrote: Hi Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
I use this to provide a backup of original data before 'cleaning' sheet1. If
required, Sheet2 provides the means of restoring the original data 'warts and all' to begin again. Wouldn't I lose this functionality just pasting values? Is the slowness because when using an xla there are in effect 2 wbooks open at the same time and as it is the xla calling for the copy paste in another wbook, the process takes longer. As I said, same code in the original wbook in xls form takes only 1 second with the same data. Geoff "Tom Ogilvy" wrote: OK - I stand corrected. Misread what you were returning in the split line. the only other alternative would be to do something akin to a pastespecial xlvalues -- Regards, Tom Ogilvy "Geoff" wrote: Hi With respect, the code copies all rows of columns A to J (last column) in the data sample I am using. I find there is little point in finding the last row for this purpose - there is no rduction in time. There are no page breaks. Geoff "Tom Ogilvy" wrote: that is just copying 1 cell. I don't see how that could take 12 seconds. do you have pagebreaks visible? If so, try turning them off. -- Regards, Tom Ogilvy "Geoff" wrote: Hi Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
Geoff,
If it is only for backup purposes, why not create a copy of the WS, then you maintain all formatting, formulae etc. Then delete the WS when ready. NickHK "Geoff" wrote in message ... I use this to provide a backup of original data before 'cleaning' sheet1. If required, Sheet2 provides the means of restoring the original data 'warts and all' to begin again. Wouldn't I lose this functionality just pasting values? Is the slowness because when using an xla there are in effect 2 wbooks open at the same time and as it is the xla calling for the copy paste in another wbook, the process takes longer. As I said, same code in the original wbook in xls form takes only 1 second with the same data. Geoff "Tom Ogilvy" wrote: OK - I stand corrected. Misread what you were returning in the split line. the only other alternative would be to do something akin to a pastespecial xlvalues -- Regards, Tom Ogilvy "Geoff" wrote: Hi With respect, the code copies all rows of columns A to J (last column) in the data sample I am using. I find there is little point in finding the last row for this purpose - there is no rduction in time. There are no page breaks. Geoff "Tom Ogilvy" wrote: that is just copying 1 cell. I don't see how that could take 12 seconds. do you have pagebreaks visible? If so, try turning them off. -- Regards, Tom Ogilvy "Geoff" wrote: Hi Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I s there a quicker way to copy paste
NickHK
mmm thought you might have something there however after testing I found the method increased the time from 12 to 21 seconds. My question remains, why should an xla cause such a dramatic increase of time over the same code in an xls form? It is definately volume related. Wbooks with only a few thousand rows whistle through with no discernable pause. Geoff "NickHK" wrote: Geoff, If it is only for backup purposes, why not create a copy of the WS, then you maintain all formatting, formulae etc. Then delete the WS when ready. NickHK "Geoff" wrote in message ... I use this to provide a backup of original data before 'cleaning' sheet1. If required, Sheet2 provides the means of restoring the original data 'warts and all' to begin again. Wouldn't I lose this functionality just pasting values? Is the slowness because when using an xla there are in effect 2 wbooks open at the same time and as it is the xla calling for the copy paste in another wbook, the process takes longer. As I said, same code in the original wbook in xls form takes only 1 second with the same data. Geoff "Tom Ogilvy" wrote: OK - I stand corrected. Misread what you were returning in the split line. the only other alternative would be to do something akin to a pastespecial xlvalues -- Regards, Tom Ogilvy "Geoff" wrote: Hi With respect, the code copies all rows of columns A to J (last column) in the data sample I am using. I find there is little point in finding the last row for this purpose - there is no rduction in time. There are no page breaks. Geoff "Tom Ogilvy" wrote: that is just copying 1 cell. I don't see how that could take 12 seconds. do you have pagebreaks visible? If so, try turning them off. -- Regards, Tom Ogilvy "Geoff" wrote: Hi Is there a quicker way to copy paste? In an xla the code below is executed by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy paste process takes 12 seconds for 21,000 rows by 10 columns. In its native form (.xls) the same code and with the same data, the copy / paste takes 1 second. Records can exceed 40,000 rows sometimes and a faster way is desirable. Setting Calculation to manual does not appear to reduce the time below 12 seconds in xla form. Is there a better way? T.I.A. Geoff Private Sub cmdExecute_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With With Sheets(1) '''get last original column lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1) '''copy original data to sheet2 .Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1") ''''''other stuff End With With Application .ScreenUpdating = True .DisplayAlerts = True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Is there a quicker way to copy a rang abd its formatting this is 2 | Excel Discussion (Misc queries) | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |