Creating New sheets

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi All

I have a programme that creates a fair number of sheets,
but if I run it about 2 - 3 times then Excel bugs out with
the error message of "Copy method of Worksheet Class
failed".

The problem isn't to do with the amount of worksheets in
the book as each time I delete them before I start, so as
far as I can workout each time that I create a new
worksheet excell puts something in memory and when it's
memory gets full it spits out an error.

I have no idea how to fix this and any advice would be
greatly appreciated.

Thanks Heaps
Jason
 
Jason,

I remember reading about this somewhere..

If memory serves me.. it has to do with the naming of the sheets.

Can it be that:
a: the sheet name gets longer then 32 chars?
b: the sheet's instance name gets corrupted on copy and becomes
something like sheet11111111111

I mean the (name) property for the sheet in VBE's properties window,
It's the first property listed in that window, not the name property.


HTH


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
I'm just throwing a wild guess into the air...

Perhaps it's the path name to the file ? Sometimes if you are
using absolute paths, I'm not sure what the maximum length
of the path can be, but I'd guess that it's 128 or 256. If the
path length goes past that then you might get various file copy
problems.

One solution in the past was to substitute drives. These days
it's called network drive letters to shorten the path. Sometimes
however the network drive letters are translated into their
network paths. So you might want to look at that as well.
 
I have Excel 2002 and after about 50 copies of the same sheet I get the same
error.
If I save the workbook, close and reopen it I can create about 18 more
copied sheets before I get the error again. Each time I save and reopen the
workbook the amount of Vb copied sheets I can make decreases. I should
mention I am copying the same sheet each time.
 
Back
Top