Copy paste slows down

[Guest]

Hi all,

I have a piece of code which simply copies a large range of data (500 rows)
to a different worksheet within the same workbook. The copied range contains
many formulas (single cell and array formulas), some of which reference
different worksheets.

My problem is: I have noticed that each time my code is run, this copy/paste
operation takes a little bit longer than before. I even wrote an infinite
loop to see the effect (over time) of repeatedly performing the same
copy/paste operation:

Public Sub myTest()

Dim myTimer As Long

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

While True

myTimer = Timer
Range("EventLog!A1:IV500").Copy Range("TempLog!A1:IV500")
Debug.Print Timer - myTimer

Wend

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Each time the loop iterates, it takes just a little bit longer. I have
noticed that when I paste the data as values only (no formulas), the problem
does not occur.

I should also mention that this problem also happens when I manually (not
via code) copy the source range and paste to the destination range.

Could this be some sort of problem with dependency trees or something like
that?

Please help!!!

Many thanks in advance - David

 

[Dave Peterson]

I don't know if this will help you, but it won't hurt much to try:

Saved from a previous post:

Turning calculation to manual, hiding the pagebreaks, and changing to normal
view can increase the speed.

You may want to do something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

 

[Guest]

Hi Dave,

Thanks for your suggestion, but no luck this time - the problem still occurs.

After some more playing around, I have noticed that this same problem does
not occur when copying and pasting data from other worksheets that contain
lots of formulas.

It seems that my problem is specific to the worksheet I am copying from (in
the example I gave before, this it the 'EventLog' worksheet), which makes me
less confident that someone else may have had this problem before.

I'll keep looking but any more help would definately be appreciated.

Cheers,

David

 

[Guest]

Hi all,

A bit of an update...

I have noticed that the worksheet I am copying and pasting has a very large
amount of formulas which link to another worksheet. I found that if I remove
these formulas, the problem is solved i.e. the looping copy/paste operation
from my first post does NOT slow down over time. This is great - this is what
I am after.

Therefore, it seems to me that if I copy and paste the worksheet when the
formulas are present, Excel tries to recreate the formula dependency tree or
something like that. Over time, this tree recreation process gets
progressively slower, despite that Application.Calculation is set to manual.

So, what I need now is some way of temporarily 'turning off' these formulas
during the copy/paste process. For example, I could rattle through the
worksheet and insert a character (e.g. '!') in front of the formulas,
essentially converting them to strings. Its just that this takes too long to
do (and then undo).

Is there any extremely quick way to stop this behaviour???

Please help!!!

David

 

[Guest]

Alternatively, does anyone know how to 'replace' a worksheet.

For example, I may have a worksheet 'A' with formulas on other worksheets
referring to certain cells on this sheet. If I delete this worksheet, the
formulas that referred to the sheet change to #REF, as you would expect.

Is there any way then to add a new worksheet, rename this sheet 'A' (the
same name as the sheet just deleted) and have all formulas automatically
refer to this new worksheet (so the #REF error is automatically resolved),
without having to physically change each formula? In essence, we have
replaced the worksheet 'A' with a new worksheet object.

I have my doubts that this is possible. I know it sounds pointless and wierd
but being able to do this may help solve my problem.

Cheers

David

 

[NickHK]

David,
I suppose you could do a replace of all the "SheetA"'s in the formulae to
some unique text like "hhhhhhhhhhhhh".
Then delete SheetA. Copy/create a new WS, rename it SheetA, then reverse the
Replacement above.

NickHK

 

[Guest]

NickHK,

Thanks for your reply. Yes you are correct, this is an option, it is just
that the replacement of the 'SheetA' string (and the subsequent reversal of
this) is too slow.

Cheers,

david