Run-time Error 1004

P

Pete

I have a macro which splits composite data (20,000+ rows) into a number
of accounts (3 at present) and then for each of these accounts splits
the data further into users (85, 156, 130). Having separated the data,
I copy each part to a temporary sheet, move this away from the main
workbook and then save the new workbook with a filename based on the
username.

The problem is that when I run it it seems to stop (having created 177
new files) with this error message "Copy method of Worksheet class
failed". The actual statement pointed to by debug is

Sheets(my_temp).Copy After:=Sheets(6)

but it has already executed this many times. I have a FOR loop to cycle
through the accounts and this will produce 85 files for the first
account and 92 for the second. If I start the FOR loop at 2, this
produces156 files for the second account and 21 for the third, i.e. 177
files before it stops. If the FOR loop starts at 3, the macro
completes.

Is there a limit to the number of new workbooks I can create?
 
P

Pete

Thanks for the link, Norman. I'm not sure if I can copy blocks of
sheets as suggested in the link, as I would then have to copy each
worksheet (rather than move a single one) in order to create individual
files, so I will still have the same number of "copy"'s. Also, the
number of users varies and is only determined at run-time. I might have
to re-think it and do only one account at a time.

Thanks,
Pete
 
Top