I'm struggling with inconsistent performance of my code. It's a long process that copies data from Excel
worksheets from my workbook (no charts; copies as a print preview rather than screenshot) and copies them to a new Word
document using the "wdPasteEnhancedMetafile" method. Each image is pasted on a new page. After pasting to Word, depending on
the prefix of the source worksheet (SPS_... RTP_...), the image is resized to a certain scale for better fit on the page.
To accomplish this resizing, each image's unique Shape number (for example, Word.Shapes(i)) is recorded at the time it is
pasted to Word to keep track of all the images. Later, more code formats those images, centering all of them in the page, and
then moving some of them to different page positions (upper left, lower right, etc.) based on those original worksheet
prefixes (RTP, LQ, etc.).
The problem I've had is that the code runs perfectly sometimes, sometimes more than once
in a row; at other times, especially when there are more worksheets to copy in the workbook, or I've had Excel open for a
while, it will suddenly "give up" resizing as it pastes to Word. There's no consistent point at which this happens; it can
happen at the beginning, or just as it's about to finish. The remaining worksheets will be pasted to Word, but will be too
small. The other formatting code, which uses the same Word.Shapes(i) ID to call these images by number, will perform
correctly, moving the data images to where they're supposed to go (and distinguishing them correctly).
reliable way for me to get the code to run is to close and reopen Excel, and close all other programs.
the long time the code takes to run and the fact that I'm copying with print preview (this is my boss' requirement), I'm
thinking it's a memory issue. I've added instructions for Excel to clear its Clipboard each time it moves to another
worksheet and copies it, but this doesn't seem to help. Is there any other way I can improve the memory or performance of my
code in this aspect? Thanks!