Copy Paste Problems

[dcoledc]

Does copying and pasting via VBA mess the sheet up?

I have been running some code that works fine initially but eventually starts to make the SHEET run very slowly. Meaing, if I reopen the book and NOT enable macros the sheet that was running the code moves very slowly.

So, does copy/paste damage the sheet in some way after repeated use? I mean, why would the sheet start running slow even when I am not running code? What is the connection?

I am looking for general understanding regarding this.

Just for information, here is what I am doing:

I have a workbook with 6 sheets in pairs. Meaning one sheet is sort of a defualt and its mate can be altered.


The user can make changes to a sheet and then choose to save or cancel the changes. If they save the changes, the default is then updated to match the changes. If they decide not to save the changes, the changed sheet is then updated to match the default.

I am running the same macro for all sheets, yet for some reason, one sheet keeps getting bogged down.

I am trying to copy/paste with formatting.
Here are the lines of code I am using.

This code updates the default:

ActiveSheet.Range(OrigRange).Copy Destination:=Sheets(X).Range(OrigRange)
ActiveSheet.Range(Note).Copy Destination:=Sheets(X).Range(Note)

This code restores the altered sheet to the default:

Sheets(X).Range(OrigRange).Copy Destination:=ActiveSheet.Rang(OrigRange)               
Sheets(X).Range(Note).Copy Destination:=ActiveSheet.Range(Note)

Honestly, I don't konw that this is the cause of my problem or not, but it seemed like a logical place to start. It is a bit challenging to diagnose the problem as it works fine initially and then random seems to bog down.

 

[Datsmart]

What is in the cells you are copying?

Just pasting a copy of text or values will not "Bog Down" your PC.
If you have extensive formulas in the cells, then that could be the problem.

Take a look at "expensive formulas" mentioned in the following post:
http://www.mrexcel.com/board2/viewtopic.php?t=266661

 

[dcoledc]

Just text in the cells. Although, there is conditional formatting in the cells that are being altered. So, the default sheet has no conditional fomratting but the sheet that the user can alter has conditional formatting.

If not the copy/paste, any other suggestions. I think an important piece of this puzzle is that once it starts to bog down, I don't have to be running code for it to continue to bog down.

When I reopen, if I don't enable macros, the one sheet still moves very slowly when doing anything on it.

 

[Datsmart]

Try turning OFF Calculation.

Click on the "Office Button"
Choose "Excel Options" at the bottom of the menu.
Click "Formulas"
Under "Calculation Options", click "Manual".

Then see if your worksheet is still "Bogged down". If not, then you have a lot of formulas that are putting a big load on your PC.

To lessen File Bloat, avoid the following:
Formats that extend over entire Rows or Columns.
Formulae that reference entire rows or columns.
In Excel version 2003 and earlier that is 65536 rows and 256 columns.
In Excel version 2007 your worksheets are now 1,048,576 Rows and 16,384 Columns.
That is 17,179,869,184 cells per worksheet. If you have formats or formulas that reference entire Columns or Rows, it will drastically slow calculations.
Avoid Graphics, even if they are hidden.
Avoid named ranges that are referenced from worksheets other than their own.
Avoid Pivot tables that have had data changed, added to, and refreshed time and again.

Press Ctrl-End and see in which cell you end up.
If you end up in a cell way below your supposed used range, you probably have formating referencing more than they need.

 

[dcoledc]

Thank you for the details. I turned calculations to manual and the sheet still updates very slowly.

As far as your other comments, I don't have any formulas or formatting over entire rows or columns.

I am not sure what you mean here:

Avoid named ranges that are referenced from worksheets other than their own.

I am still fixated on the fact that the problem only arises on one of the sheets when I am running the same code on all three sheets.

Any other thoughts?

Thanks

 

[Datsmart]

Try manually building a new worksheet and copying your data to it.
(Do not use the Right Click Tab, Move/Copy worksheet option.)

Then see if that sheet is slow. Sometimes you just have to start fresh...

By the way, be sure you turn Calculation back to Auto so your formulas up-date.

 

[dcoledc]

Thanks again for your feedback. I have rebuilt the sheet that was running slow and it does seems to work fine now. My concern is that some of my code is causing it to happen over time.

I don't want this happening to users over time.

I just noticed something new and was wondering what you thought about it and wondering if it is somehow related.

Originally, I said there are 6 sheets but there are actually 9, grouped in 3's. One sheet in the group is the sheet the user sees and can alter, one sheet stores the users changes and one sheet is an orignal unaltered, in case they want to restore defaults.

I have conditional formatting in all the sheets the user sees. When they save their changes, the conditional formatting is preserved. When they restore defaults, only the conditional formatting is preserved on the first sheet, the sheet that in the past started to run slow. I am using the EXACT same code to restore each of the sheets.

Why would the conditional formatting be preserved on one sheet and not the others?

Here is the code I am using to restore defaults:

Sub Restore()
SkipThis = True
Select Case MsgBox("Are you sure you wish to restore template text to original form?", vbYesNo + vbQuestion, "Nerve Charts Unlimited")
    Case vbYes
        Sheets("BLOCKED").Unprotect Password:="17Lj041kM"
        Sheets("BlOriginal").Range("U6:V39").Copy Destination:=Sheets("BLOCKED").Range("U6:V39")
        Sheets("BlOriginal").Range("W6:Z39").Copy Destination:=Sheets("BLOCKED").Range("W6:Z39")
        Sheets("BLOCKED").Protect Password:="17Lj041kM"
        Sheets("BLOCKED").EnableSelection = xlUnlockedCells
        Sheets("STANDARD").Unprotect Password:="17Lj041kM"
        Sheets("StOriginal").Range("U6:Y31").Copy Destination:=Sheets("STANDARD").Range("U6:Y31")
        Sheets("STANDARD").Protect Password:="17Lj041kM"
        Sheets("STANDARD").EnableSelection = xlUnlockedCells
        Sheets("QUESTION").Unprotect Password:="17Lj041kM"
        Sheets("QuOriginal").Range("U6:V39").Copy Destination:=Sheets("QUESTION").Range("U6:V39")
        Sheets("QUESTION").Protect Password:="17Lj041kM"
        Sheets("QUESTION").EnableSelection = xlUnlockedCells
        SkipThis = False
    Case vbNo
        Exit Sub
End Select
ActiveSheet.Range("d5").Select
Application.CutCopyMode = False
SaveCharts
End Sub

ANy thoughts?

Thanks yet again. I am still learning

 

[dcoledc]

I think the last post has opened up the possible cause. I still can't figure out the last post, but I noticed when I went under the manage rules for condtional formatting, I had a ton of rules, I assume because of the copy/pasting that was going on. I need some way of preserving the condtional formatting without coping it over and over.

Is there a way to do that?

 

[Datsmart]

Are you using Code to Copy and Paste or are you doing it manually?

If your Conditional Formatting is getting bloated, I would suggest putting together some code to clear all the Conditional Formatting and then re-write it.

If you need help doing this, Turn on the macro recorder, go through the steps, paste the code here and maybe we can help fine tune it for you.

 

[Cease]

I don't know if you've tried this or not, but it would be pretty simple to change it to paste values or formulas only through VBA, making the conditional format "bloating" problem no longer an issue.

Values:

             Selection.Copy
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

or Formulas:

              Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
               SkipBlanks:=False, Transpose:=False

Not sure how you could get these to work in your macro, but I do something similar on some of my pages using these - both straight from the macro recorder.

Maybe it can help.

GL!