Why does a worksheet copy take so long?

[B540Glenn]

I created a VBA to generate multiple monthly reports. This VBA copies worksheets from four source files to a skeleton file and generates reports from the skeleton. The skeleton file contains report templates and the VBA to populate the templates for a facility or for the facilities combined. When other users from multiple sites run the program, it executes in 45-60 seconds. When I run it (same file in same location), it takes almost 5 minutes. There must be something different about my machine.

I found the delay in processing to be this Statement:

Workbooks(CurrFile).Sheets(FacilityName).Copy After:=Workbooks(Skeleton).Sheets(Workbooks(Skeleton).Sheets.Count)

I open a source file then copy a worksheet from the source to the skeleton file. I do this 4 times. Each copy takes about a minute.

When I run the VBA in Safe Mode, it runs quickly, 40-50 seconds. This led me to believe that an add-in contributed to the poor performance. I removed ALL the add-ins. Without any add-ins, in regular mode, the VBA still takes 5 minutes to run, in safe mode without add-ins, 40-50 seconds.

What is interfering with the worksheet copy on my machine?
Where else can I look to remove the obstacle?
Got any suggestions?

Thanks for your help,
Glenn

 

[Redwolfx]

Another shot in the dark could be memory related.

I assume you are all using similar Hardware?

You may want to bring up your Task Manager and see if there are any processes that are eating your memory or CPU. I've also heard that sometimes over utilization of your resources can actually slow down processing as well.

If you only need values to reach the Skeleton Sheet you may just want to use = I know this is only happening on your PC, but that doesn't mean there isn't a way to clean up to code so it runs faster on everyone's PC.

If you are looking to revamp the sheet as a whole Copyfromrecordset is a very fast tool as well.

I'll admit, you have a relatively unique issue, one that will be difficult to troubleshoot as we do not have access to your PC. Even if we were able to recreate your environment apparently there is a good chance that we still would not experience the issue you are having. It almost seems that this may be an issue better resolved by your IT department than the people here at MrExcel.com

 

[B540Glenn]

Another shot in the dark could be memory related.

I assume you are all using similar Hardware?

You may want to bring up your Task Manager and see if there are any processes that are eating your memory or CPU. I've also heard that sometimes over utilization of your resources can actually slow down processing as well.

If you only need values to reach the Skeleton Sheet you may just want to use = I know this is only happening on your PC, but that doesn't mean there isn't a way to clean up to code so it runs faster on everyone's PC.

If you are looking to revamp the sheet as a whole Copyfromrecordset is a very fast tool as well.

I'll admit, you have a relatively unique issue, one that will be difficult to troubleshoot as we do not have access to your PC. Even if we were able to recreate your environment apparently there is a good chance that we still would not experience the issue you are having. It almost seems that this may be an issue better resolved by your IT department than the people here at MrExcel.com

You are correct. We all have similar hardware/software configurations. I'm using 1.2gb of 2gb when running the script. That includes e-mail and all network connection software being active.

I'll look into the CopyFromRecordset method. Making it faster is a good thing.

Sadly, our IT department is of little help in these types of matters. They're great if you want your password reset or software installed, but we're on our own for anything out of the ordinary.

Another idea I had was the Clipbook service. The script ran the same with the service started and disabled.

I think I'll concentrate on the differences between regular mode and safe mode. It runs as expected in safe mode. Whatever safe mode disables must be the culprit.

Thanks for your help,
Glenn