VBA Code is running really slowly

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.

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.

Hi,

faster with ScreenUpdating to False !

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.

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.

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

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!