Paste Special drastically slowing down macro?

You could try dropping all the Selects and Selections:

Please Login or Register  to view this content.

Switch off calculation, screen updating and event handling before and back on again afterwards, if you're not doing that already.

To enable testing and, perhaps a more specific solution, you could upload a de-sensitized sample workbook.

Regards, TMS

Originally Posted by berlan

alternatively,

Please Login or Register  to view this content.

and you will have less in the clip.

I tried this and it definitely reduces the processing of the macro but it still takes awhile. My macro is pretty extensive creating over 30 workbooks. Before I added this copy and paste as values, the entire macro would take maybe just over a minute to complete.

Then I added 3 dynamic copy and paste as values in 3 different columns. These columns can range from having 1,000 to over 5,000 rows of data. This is across 30+ workbooks. Adding these functions increased the processing time of my macro an additional 3 minutes even after using berlan's suggestion.

I'm positive this copy and paste as values is the only thing causing the issue, because if I take this part of the code out, the macro finishes in about a minute. So is it just the nature of the function: copying and pasting 1,000 - 5,000 rows three different times across 30+ workbooks that increases the macro processing time over 3 additional minutes?

Oh and in regards to your suggestions TMS, screen updating is turned off for the entire macro.

Set calculation to Manual before and to Automatic afterwards.

How do you determine Lastrow?

Please Login or Register  to view this content.