Hi
A brief summary of where I am with this. Yesterday I posted the question
when using code from an xla does it take 12 seconds to copy some 20,000 rows
by 10 columns when the same code from an xls wbook, same data, takes only 1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has any
discernable effect.
To save folk having to find the code in the original post here it is again.
Appreciate any thoughts

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,
I can't see any any noticable difference running code (copy/paste) on 20000
x 12 cells from an .xls or xla, both about 1 sec.
Not sure if it is your method of getting the required range, but does
something like this help:
Sheets(1).Range("IV1").CurrentRegion..Copy
Destination:=Sheets(2).Range("A1")

NickHK

"Geoff" wrote in message
...
Hi
A brief summary of where I am with this. Yesterday I posted the question
when using code from an xla does it take 12 seconds to copy some 20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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

Sheets(1).UsedRange.copy Sheets(2).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de news:
...
Hi
A brief summary of where I am with this. Yesterday I posted the question
when using code from an xla does it take 12 seconds to copy some 20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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
No i am happy with the range finding statement BUT...
You are right about the speed - this is curious......
Your test result caused me to take a copy of this wsheet, allegedly 21,053
rows by 10 columns and create a new wbook. I ran the code from the xla and,
like you, found it took only 1 second.... mmmmm.
I have used the original wbook because to date it represents the largest
wbook. My code copies the entire column (as I found very little advantage to
be gained by limiting the copy by rows) and I now begin to wonder if
somewhere in the rest of the original rows there is some hidden formatting
for instance which might be affecting the situation.
Even so, if copy pasting from the xla using a new work book containing the
21,053 rows takes 1 second why does copying only 3 times times the amount
take 12 seconds from the original wbook?

Geoff

"NickHK" wrote:

Geoff,
I can't see any any noticable difference running code (copy/paste) on 20000
x 12 cells from an .xls or xla, both about 1 sec.
Not sure if it is your method of getting the required range, but does
something like this help:
Sheets(1).Range("IV1").CurrentRegion..Copy
Destination:=Sheets(2).Range("A1")

NickHK

"Geoff" wrote in message
...
Hi
A brief summary of where I am with this. Yesterday I posted the question
when using code from an xla does it take 12 seconds to copy some 20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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 you copy/paste a lot of formats/names etc that you do not really need, it
will not make the process any faster. How much slower will depend on ...many
things.

NickHK

"Geoff" wrote in message
...
NickHK
No i am happy with the range finding statement BUT...
You are right about the speed - this is curious......
Your test result caused me to take a copy of this wsheet, allegedly 21,053
rows by 10 columns and create a new wbook. I ran the code from the xla
and,
like you, found it took only 1 second.... mmmmm.
I have used the original wbook because to date it represents the largest
wbook. My code copies the entire column (as I found very little advantage
to
be gained by limiting the copy by rows) and I now begin to wonder if
somewhere in the rest of the original rows there is some hidden formatting
for instance which might be affecting the situation.
Even so, if copy pasting from the xla using a new work book containing the
21,053 rows takes 1 second why does copying only 3 times times the amount
take 12 seconds from the original wbook?

Geoff

"NickHK" wrote:

Geoff,
I can't see any any noticable difference running code (copy/paste) on
20000
x 12 cells from an .xls or xla, both about 1 sec.
Not sure if it is your method of getting the required range, but does
something like this help:
Sheets(1).Range("IV1").CurrentRegion..Copy
Destination:=Sheets(2).Range("A1")

NickHK

"Geoff" wrote in message
...
Hi
A brief summary of where I am with this. Yesterday I posted the
question
when using code from an xla does it take 12 seconds to copy some
20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes
only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has
any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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 Ardus
No that still took 12 seconds.
I tested further after NickHk's reply and specifically set the range from A1
to last cell, which as you suggest should be equivalent to UsedRange in this
case but found no change.
I conclude there must be some kind of formatting which is being copied.
But what I have now is:
If I manually create a new wbook and copy only the visible data, 21,053 x
10, to it and run the xla the copy paste takes only 1 second.
The same applies if I disable the xla and copy ALL of sheet1 into the
original xls code wbook and run the code from there.
The exception is when I programatically create a new wbook using:
Sheets(1).UsedRange.Copy Destination:=procFile.Worksheets(1).Range("A1").
When I run the xla here, I get 12 seconds. In my opinion then, the above
statement is somehow affecting the procedure. But how??

Geoff

"Ardus Petus" wrote:

Sheets(1).UsedRange.copy Sheets(2).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de news:
...
Hi
A brief summary of where I am with this. Yesterday I posted the question
when using code from an xla does it take 12 seconds to copy some 20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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,
Any benefit if you toggle
Application.Calculation
Application.EnableEvents

NickHK

"Geoff" wrote in message
...
Hi Ardus
No that still took 12 seconds.
I tested further after NickHk's reply and specifically set the range from
A1
to last cell, which as you suggest should be equivalent to UsedRange in
this
case but found no change.
I conclude there must be some kind of formatting which is being copied.
But what I have now is:
If I manually create a new wbook and copy only the visible data, 21,053 x
10, to it and run the xla the copy paste takes only 1 second.
The same applies if I disable the xla and copy ALL of sheet1 into the
original xls code wbook and run the code from there.
The exception is when I programatically create a new wbook using:
Sheets(1).UsedRange.Copy Destination:=procFile.Worksheets(1).Range("A1").
When I run the xla here, I get 12 seconds. In my opinion then, the above
statement is somehow affecting the procedure. But how??

Geoff

"Ardus Petus" wrote:

Sheets(1).UsedRange.copy Sheets(2).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de
news:
...
Hi
A brief summary of where I am with this. Yesterday I posted the
question
when using code from an xla does it take 12 seconds to copy some
20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes
only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has
any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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 NickHK
Tried Calculation to manual with no benefit, to my surprise. Re
EnableEvents, I use DoEvents to increment a progress bar so presume by making
EnableEvents = False it would prevent that function.
I am going to focus on that part of my reply to AP - what is happening when
I programatically create a new wbook using UsedRange.Copy. As I said if i
manually copy ALL Sheet1 to the original xls of the xla I get 1 second. If I
manually copy the specific data range to a new wbook and run the xla I get 1
second. Only when I programatically create a new wbook using UsedRange.Copy
do I get the long delays.

Maybe I should experiment a bit more with PasteSpecial when creating a new
wbook. But as I recall when I was doing the code for that, I got 'Serious
Errors' and only with the suggestion from AP to use UsedRange.Copy did the
proc work.

But I am convinced there is some kind of formatting issue here because I
have tried other largish wbooks and found the same. But importantly, not on
every one - so it looks as though someone is treating there data differently.

Geoff



"NickHK" wrote:

Geoff,
Any benefit if you toggle
Application.Calculation
Application.EnableEvents

NickHK

"Geoff" wrote in message
...
Hi Ardus
No that still took 12 seconds.
I tested further after NickHk's reply and specifically set the range from
A1
to last cell, which as you suggest should be equivalent to UsedRange in
this
case but found no change.
I conclude there must be some kind of formatting which is being copied.
But what I have now is:
If I manually create a new wbook and copy only the visible data, 21,053 x
10, to it and run the xla the copy paste takes only 1 second.
The same applies if I disable the xla and copy ALL of sheet1 into the
original xls code wbook and run the code from there.
The exception is when I programatically create a new wbook using:
Sheets(1).UsedRange.Copy Destination:=procFile.Worksheets(1).Range("A1").
When I run the xla here, I get 12 seconds. In my opinion then, the above
statement is somehow affecting the procedure. But how??

Geoff

"Ardus Petus" wrote:

Sheets(1).UsedRange.copy Sheets(2).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de
news:
...
Hi
A brief summary of where I am with this. Yesterday I posted the
question
when using code from an xla does it take 12 seconds to copy some
20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes
only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has
any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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 NickHk
If you are still following the thread :)
I have established an improved methodology. The process was with a non 3
wsheet wbook:
1. create a new std wbook and copy paste the original wsheet to it
2. save the new wbook BUT leave it open for other processing
3. close the original wbook

I have found if I close the new wbook and then re-open it immediately for
other processing which amongst other things creates a backup on Sheet2 then
this copy paste takes 1 second as expected.

The only thing I have to puzzle out now is why does it takes so long to copy
data from the original to the new wbook.

Geoff

"Geoff" wrote:

Hi NickHK
Tried Calculation to manual with no benefit, to my surprise. Re
EnableEvents, I use DoEvents to increment a progress bar so presume by making
EnableEvents = False it would prevent that function.
I am going to focus on that part of my reply to AP - what is happening when
I programatically create a new wbook using UsedRange.Copy. As I said if i
manually copy ALL Sheet1 to the original xls of the xla I get 1 second. If I
manually copy the specific data range to a new wbook and run the xla I get 1
second. Only when I programatically create a new wbook using UsedRange.Copy
do I get the long delays.

Maybe I should experiment a bit more with PasteSpecial when creating a new
wbook. But as I recall when I was doing the code for that, I got 'Serious
Errors' and only with the suggestion from AP to use UsedRange.Copy did the
proc work.

But I am convinced there is some kind of formatting issue here because I
have tried other largish wbooks and found the same. But importantly, not on
every one - so it looks as though someone is treating there data differently.

Geoff



"NickHK" wrote:

Geoff,
Any benefit if you toggle
Application.Calculation
Application.EnableEvents

NickHK

"Geoff" wrote in message
...
Hi Ardus
No that still took 12 seconds.
I tested further after NickHk's reply and specifically set the range from
A1
to last cell, which as you suggest should be equivalent to UsedRange in
this
case but found no change.
I conclude there must be some kind of formatting which is being copied.
But what I have now is:
If I manually create a new wbook and copy only the visible data, 21,053 x
10, to it and run the xla the copy paste takes only 1 second.
The same applies if I disable the xla and copy ALL of sheet1 into the
original xls code wbook and run the code from there.
The exception is when I programatically create a new wbook using:
Sheets(1).UsedRange.Copy Destination:=procFile.Worksheets(1).Range("A1").
When I run the xla here, I get 12 seconds. In my opinion then, the above
statement is somehow affecting the procedure. But how??

Geoff

"Ardus Petus" wrote:

Sheets(1).UsedRange.copy Sheets(2).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de
news:
...
Hi
A brief summary of where I am with this. Yesterday I posted the
question
when using code from an xla does it take 12 seconds to copy some
20,000
rows
by 10 columns when the same code from an xls wbook, same data, takes
only
1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact
copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has
any
discernable effect.
To save folk having to find the code in the original post here it is
again.
Appreciate any thoughts

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