Copy and paste named range at end of loop causing massive slowdown over time

Hi all,

So I'm currently running a fairly chunky macro that processes a load of data from different sources into, a series of nicely formatted tables on a single worksheet. Each 'table' represents one running of my loop. At the beginning and end of the loop, it pastes a named range before and after the 'table' - a range of colum headers on top and a series of pre-defined user inputs underneath, taken from a template worksheet.

The loop has to iterate through about 10,000 'tables'. After about 500 or so, the performance begins to tail off dramatically going from about 300 loops per minute to 60. I spent the best part of a day debugging and narrowed the bottleneck down to the lines of code that paste the header / footer ranges. On removing them, performance stays fast.

The macro is far too large to paste in here, but below is one section of the code where it pastes in the footer range. The line's immediate context is below the body of this post.

Please Login or Register  to view this content.

I only copy and paste the range below the table (or cluster, in the code's parlance).

Does anybody have a clue why this would be slowing me down so radically and if so, is there anything I can do about it? I've read lots of different tips and tricks about how to optimise VBA copying and pasting, but none of them seem to have made a difference to date. Kind at my wits' end here, so any help would be massively appreciated.

Cheers,
Dan

Please Login or Register  to view this content.

Dan

Where is the data coming from?

Dan

The reason I asked was because the problem may not be with copying of the named range, it could be something in the rest of the code.

It might be an idea to post at least some of the rest of the code so we can, hopefully get an idea what's going on.

An explanation in words might help too.