Oddly slow running code, under very specific circumstances

[Chris83]

Hello all,

I am working on a project generate worksheets for our lab. The code runs great, it's lightning fast, assuming you run it right after opening the workbook. For some reason if you open the work book, add or change a value for the list input and then run the VBA it takes upwards of 2 mins to run and the CPU usage spikes to around 30% according to the task manager, memory usage seems un-affected. If you make the changes, save it, re-open it and then run it. 6 seconds tops. I have tracked the issue down to the copy worksheet line using msgboxs. For some reason


Sheets(strTemplate).Copy After:=Sheets(strLocation)

takes forever to under these circumstances, and just adding a blank worksheet has the same issues. I have tired removing conditional formatting, suspending auto-calculations, screen updating, ensuring all named groups exist, clearing the clip board, both system and excel, and anything I can think of/google to get it to run faster. Which it does, but this issue still remains.

I am hoping someone has some insight into how to fix this. I am really hoping that its something ridiculously easy that I am missing.

Also I apologize for not posting any of the code but the workbook contains sensitive information that is referenced a lot. It's running on Excel 2016 64 bit, in case you need to know.

Thanks

[Paul_Hossler]

If you have any event handlers make sure you have



Application.EnableEvents = False



and



Application.EnableEvents = True


when something might trigger them

Otherwise, you'll probably have to fake the data and post the workbook for people to look at

[georgiboy]

Another aspect worth considering is whether the macro has inadvertently created any excessive blank space on the 'strTemplate' sheet, particularly when it approaches a point where it takes a long time. Specifically, does the vertical scroll bar scroll far beyond the actual data on the sheet that is being relocated?

[Chris83]

Thanks for the suggestion, but I am already tried that. You are correct in that I may have to post the code, which would be interesting as it's kind of a lot.


If you have any event handlers make sure you have



Application.EnableEvents = False



and



Application.EnableEvents = True


when something might trigger them

Otherwise, you'll probably have to fake the data and post the workbook for people to look at

[Chris83]

A good suggestion that I hadn't thought of. Unfortunately, no, the copied tab is just A1 to N21, there are a bunch of cell formulas, but automatic updates are turned off, and some conditional formatting, but removing it didn't help. The fact that the same issue happens when you insert a completely blank sheet with .add as well as with .copy seems to point at a general excel issue. That, and the fact it runs fine if you change nothing after opening it. I may, as dumb as this sounds, have to make it save, close and relaunch itself and have it auto run the code as an opening condition. It's the stupidest work around and I really don't want to do it but that may be the way to go.


Another aspect worth considering is whether the macro has inadvertently created any excessive blank space on the 'strTemplate' sheet, particularly when it approaches a point where it takes a long time. Specifically, does the vertical scroll bar scroll far beyond the actual data on the sheet that is being relocated?

[Paul_Hossler]

1. The WB might be currupted. Create a blank workbook and copy / paste the data and macros etc.

2. Try cleaning the macros

http://www.appspro.com/Utilities/CodeCleaner.htm

[Chris83]

Thanks for the tip, about cleaning the macros, unfortunately its 64 bit excel so Code Cleaner is out.

[Aflatoon]

You can do it manually by exporting, removing and re-importing all the modules. Not sure it will help based on your description of the problem, but it never hurts to do on a project that has built-up over time.

[Dave]

Hi Chris83. Maybe just for funzies, trial the following code to use a collection to transfer the sheet. HTH. Dave

Dim ShtCollect As Collection
Set ShtCollect = New Collection
ShtCollect.Add ThisWorkbook.Sheets(strTemplate)
ShtCollect(1).Copy After:=ThisWorkbook.Sheets(strLocation)

[Paul_Hossler]

Guess you'll have to post the workbook so we can take a look