.copy problems

[Jim A]

Hi -
I have two identical sheets. One is for "reseting" the other after a user
enters in numbers and wants to reset sheet to its original state. Sheet1 is
for user entry and Sheet2 is the duplicate to copy to Sheet1. They contain
only formulas and conditional formats. The button I use to run the .copy
macro is on rows in Sheet1 that are not in the range of cells copied.

QUESTION: Why when I run the macro several times (copying Sheet2 to Sheet1)
Sheet1 operations get progressively slower to freezing the worksheet
altogether. Even selecting cells refreshes the page very slow. It clocked
at over 14 seconds before it just quit opening Sheet1.

My code:
Set r1 = Sheets("Sheet1").range("A3:AU48")
Set r2 = Sheets("Sheet2").Range("A3:AU48")
r2.Copy r1

What seemed to work better is:
Sheets("Sheet1").Range("A3:AU48").Value = _
Sheets("Sheet2").Range("A3:AU48").formula

But I do not know why...
Can anybody offer an explanation - Thanks - Jim A

 

[OssieMac]

Hi Jim,

Can't say that I really know the answer and don't know if the following will
help but because your alternative method appears to work without assigning
the ranges to variables I wonder if the memory is being held by the assigned
variables so try the following immediately after the copy line to clear the
variables.

Set r1 = Nothing
Set r2 = Nothing

You could also try clearing r1 before the copy.

 

[Ragdyer]

Just a thought.

Why not let XL do the copying for you?

Just save the original blank sheet as a template.

The user can open a new sheet by simply going to:
<File> <New>,
and click on the sheet name under the General tab.

XL opens a copy of the original for use ... preserving the original.

The user can either "throw it away" after use, or save it with a name.

 

[Dave Peterson]

Maybe you can try a couple of tests...

1. Change the code to copy, paste special|values
r1.copy
r2.pastespecial paste:=xlpastevalues

2. Change to a different printer (and use your original copy|paste code)

My guess is that since you're pasting the range, you're pasting formats, too.
Changing to .values should work a lot like the .value = .value (or .value =
..formula).

And if the formatting is causing the trouble, then maybe it's really excel
determining how to display the pasted range. (Excel will use the printer driver
for this kind of stuff.)

But those are just guesses.

 

[Jim A]

I'll try that, and then clock the speed - thanks - JA