Copy and Paste make Excel run slow

[dcoledc]

I have created a program with 3 visible sheets and 3 hidden sheets. The hidden sheets store information from the visible sheets. When 1 of the visible sheets is changed, the user has the option of saving the changes. If they choose to save the changes it updates the hidden page related to it.

In creating this program, I have repeated tested this, typically using only one of the sheets. I tell you this only to find out if doing this is the cause of my problem.

My problem is that the program works fine for awhile and then suddenly begins running EXTREMELY slow, but only on the sheet that I repeatedly tested on. Running the same code on the other sheets and it runs fine.

The first time this happened, I thought perhaps the one sheet got corrupted somehow, so I completely recreated it. It seemed to fix the problem, but only for a while b/c it is back.

Some sample code:

        Select Case MsgBox("Do you wish to save the changes?", vbYesNo + vbQuestion, "Nerve Charts Unlimited")
            Case vbYes
                ActiveSheet.Unprotect
                ActiveSheet.Range(PatName).Select
                ActiveSheet.Range(OrigRange).Copy Sheets(X).Range(OrigRange)
                ActiveSheet.Range(Note).Copy Sheets(X).Range(Note)
                ActiveSheet.Shapes("TextBox 3").Fill.ForeColor.SchemeColor = 10
                ActiveSheet.Shapes("TextBox 3").TextFrame.Characters.Text = "Change Template"
                ThisWorkbook.Save
                SetBack = ThisWorkbook.Saved
            Case vbNo
                Sheets(X).Range(OrigRange).Copy ActiveSheet.Range(OrigRange)
                Sheets(X).Range(Note).Copy ActiveSheet.Range(Note)
                ActiveSheet.Unprotect
                ActiveSheet.Shapes("TextBox 3").Fill.ForeColor.SchemeColor = 10
                ActiveSheet.Shapes("TextBox 3").TextFrame.Characters.Text = "Change Template"
                ActiveSheet.Range(PatName).Select
                ActiveSheet.Range(PatName) = "Name"
                ActiveSheet.Range(StartDate) = Date
                'Change = False 'skips update in screening sheet
                ThisWorkbook.Saved = SetBack
        End Select

I did not include all the code from the sub, just the code that is being run when excel starts running slowly. All the variables used here are defined, I just didn't show the code.

Does anyone see something wrong with this code? Perhaps the way I am copying and pasting? Or does something happen behind the scenes when I copy and paste over and over again?

Also, the sheet itself becomes very slow. Scrolling up and down the sheet becomes extremely slow even when I am not running code.

Thoughts please, this is driving me nuts.

Thanks

 

[Sektor]

Add this line at the top of Sub:

Application.ScreenUpdating = False

And before End Sub:

Application.ScreenUpdating = True

 

[sijpie]

In the vbYes you have this line:

               ActiveSheet.Range(OrigRange).Copy Sheets(X).Range(OrigRange)

Should that not read:

               ActiveSheet.Range(OrigRange).Copy destination:=Sheets(X).Range(OrigRange)

I would rewrite it anyway to make it slightly easier to read:

       With Activesheet
            .Unprotect
            .Shapes("TextBox 3").Fill.ForeColor.SchemeColor = 10
            .Shapes("TextBox 3").TextFrame.Characters.Text = "Change Template"
           Select Case MsgBox("Do you wish to save the changes?", vbYesNo + vbQuestion, "Nerve Charts Unlimited")
            Case vbYes
                .Range(PatName).Select
                .Range(OrigRange).Copy Destination:=Sheets(X).Range(OrigRange)
                .Range(Note).Copy Sheets(X).Range(Note)
                ThisWorkbook.Save
                SetBack = ThisWorkbook.Saved
            Case vbNo
                Sheets(X).Range(OrigRange).Copy Destination:=.Range(OrigRange)
                Sheets(X).Range(Note).Copy Destination:=.Range(Note)
                .Range(PatName).Select
                .Range(PatName) = "Name"
                .Range(StartDate) = Date
                'Change = False 'skips update in screening sheet
                ThisWorkbook.Saved = SetBack
        End Select
     end With

 

[sijpie]

After the end with you could also add:

Application.CutCopyMode = false

to clear the paste buffer

 

[dcoledc]

Thank you both for the feedback. I am fairly new to VBA and have a few questions

Sektor - what do adding the application.screenudpating do? I know that it prevents you from seeing the changes happen, but that is my only knowledge for its use.

sijpie - I added the Destination before each paste and I added the Application.CutCopyMode = false at the very end of the sub, is that where it should go? Also, does that line of code clear the clipboard?

Also, I still need to know why only one sheet is running slow. I set the variable so this code code be used for all 3 sheets and only one sheet runs slow. Do you think it is because I copy/pasted so many times during testing? If so, will I need to recreate the sheet again and this time use the new code?

Thanks

 

[sijpie]

code runs a lot faster if windows does not need to update the screen, plus the user will not see any flickering of the screen as active windows change etc. All that is accomplished with the

application.screenupdating=false

the

application.cutcopymode=false

indeed clears the clipboard

It is not clear from your code why one page should give issues. It could be that if a lot is being copied and pasted, that excel needs to maintain its undo.

 

[dcoledc]

Thanks for the feedback. The informatuon being copied is almost identical. I guess I will rebuild that sheet once again, use the suggested changes and see if that solves it. My main concern is that this workbook is for distribution. I don't want it failing when it is out there.

Thanks again.