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

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
>

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
> >

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
> > >

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
> > > >

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" <Geoff@discussions.microsoft.com> wrote in message
news:0E97546E-2511-439C-B86B-E42389319F7C@microsoft.com...
> 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
> > > > >

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" <Geoff@discussions.microsoft.com> wrote in message
> news:0E97546E-2511-439C-B86B-E42389319F7C@microsoft.com...
> > 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
> > > > > >
>
>
>