Chris83
05-24-2023, 06:27 AM
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
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