Copying worksheet error

[Newbie80]

Hi,
I have made a macro that copies worksheets from diffrent workbooks, and
then places the worksheets into the same workbook. This is a pretty
simpel function, but when I have copied to many sheets I recive an
error 1004. Is there an easi way to come around this ?

This is in a loop:
sourceWb.Worksheets("Prodject-" & year).Copy
after:=thisWb.Sheets(thisWb.Worksheets.count)

 

[Guest]

hi,
Not sure what the problem is that cause it. it has something to do with the
clipboard.
from experience, i have learned not to use the copy command in a loop.
usually crashes the macro with memory error messages. using it once or twice
in a macro is ok but not in any kind of loop. there are other ways.
and this is not unique to xl. i had the same problem with lotus 123. and the
cut command is even worst.
try this just before the copy command........

application.cutcopymode = false

this will clear your clipboard just before the copy and may solve your
problem. post back if it doesn't.

regards
FSt1

 

[Newbie80]

Still fails

 

[Norman Jones]

Hi Newbie80,

See MSKB # 210684:

'Copying worksheet programmatically causes run-time error 1004 in Excel'

http://tinyurl.com/9283s

 

[Newbie80]

This is the problem, seems like I need to close and reopen th
workbook.

Seems to be a built in problem/bug in the Copy-method :