VBA Code is running really slowly

**behrensf84** · 06-05-2014, 05:02 PM

Hi,

I trying to make a macro that does some copying and pasting between worksheets, but for some reason it is running really slowly. it also seems to get slower with time, if I try to run a 2nd and 3rd time to the point that the code doesn’t completely run at all, and I have to Ctrl+Alt+Delete, just to be able to close excel.

I think it may be because there is some code in the worksheet that is designed to run whenever there is a worksheet_change sub, however, I placed the entire worksheet_change code inside an if statement to try to skip it but the code still runs slowly. It fails when it gets to the worksheet_change

I cant attach the file, because it's more than 1MB, but would anyone know if there is a way to deactivate a "Private Sub_Worksheet Change" sub so that it does not run, and then activate it back up again?

thanks, I really appreciate the help.

**daffodil11** · 06-05-2014, 05:14 PM

Maybe something along the lines of:

Sub KittenExplosion()

Application.EnableEvents = False
application.Calculation =xlCalculationManual

'copying and pasting stuff

Application.EnableEvents = True
application.Calculation =xlCalculationAutomatic

End Sub

This will prevent the workbook from calculating formula outputs or running additional subs while the macro is running.

Edit: I found an additional solution on the interwebs.

Delcare a global variable at the top of your module, and set it to on while the macro runs. Worksheet_Change event can see if it's on, and stop if it is.

Public CopyRunning As String

Sub CopyPasteMacro() 
    CopyRunning = "On" 

    'copy and paste stuff 

    CopyRunning = "Off" 
End Sub

and then worksheet_change macro:

Private Sub Worksheet_Change(ByVal Target As Range) 
    If CopyRunning = "On" Then Exit Sub 

    'normal code stuff
End Sub

**Richard Buttrey** · 06-05-2014, 05:16 PM

Hi,

Try incorporating an initial Application.EnableEvents = False, and end with the corollary Application.EnableEvents = True

**behrensf84** · 06-06-2014, 09:43 PM

Thanks everyone.

both methods do help a bit, but I think that the problem is also that I'm just trying to copy and paste too much information in one go, and excel is have trouble to graphically display all the information.

**Marc L** · 06-06-2014, 09:45 PM

Hi,

faster with ScreenUpdating to False !

**behrensf84** · 06-07-2014, 03:09 PM

Hmmm, its still taking a long time to run, do you have any other ideas? the problem seems to be that Im copying anf pasting some really long formulas. Evenwhen I try to do a manual copy and paste, the sheet does not update.

**Richard Buttrey** · 06-07-2014, 03:40 PM

Hi,

Maybe it's time to upload the workbook (cut down as much as necessary in order to upload) so that we can see the request in context.

One technique I often use to identify the problem area is to step through the macro with the F8 key and see how long the cursor takes to become live and postion itself on the next code line. Usually of course it's instantaneous so doing this you should find which is the line that is taking the time.

You can effectively 'deactivate' any procedure by using a variable and testing the variable as the first line of the procedure. So assuming you want to control the procedure from say the A1 value in Sheet1 then put say the word 'Stop' in A1 and in the procedure

If Sheet1.Range("A1") = "Stop" Then End 'or Exit Sub

**behrensf84** · 06-07-2014, 04:32 PM

Hi Richard,

The code always fails when it gets to the line "ActiveSheet.paste". I think that the reason is because there is just too much data for excel to deal with. the file is about 8 MBs and has several tabs, all of which depend on the tab I'm working on. infact, even when I try to do manually what the code does automatically, excel still runs into trouble when it tries to paste the information.

I'll try to trim down the work file, but its a bit difficult to get it down to 1MB. I'll try to post the file in two pieces with the relevant information.

thanks for the help.

**jolivanes** · 06-07-2014, 04:41 PM

Showing the code would certainly help I am sure.
Show where the code is, Regular Module, Sheet Module or This Workbook Module.

**behrensf84** · 06-07-2014, 06:06 PM

Hi everyone,

I think I figured out the problem. the issue is the conditional formating, which apparently when it gets copied, makes excel really slow. I changed the code from "activesheet.paste" to "Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone", and that seems to make things run much faster.

thanks for all the help!

VBA Code is running really slowly

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Re: VBA Code is running really slowly

Thread Information

Users Browsing this Thread

Similar Threads

Code running really slowly

[SOLVED] Code running slowly

[SOLVED] Code running slowly any way of speeding up processing time

Code running slowly

Paste code running extremely slowly...

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions