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


"Jason" wrote:

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

That is in Excel 97 only. Believe it was fixed in Excel 2000.

--
Regards,
Tom Ogilvy

keepitcool wrote in message
...
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


"Jason" wrote:

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

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.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
That is in Excel 97 only. Believe it was fixed in Excel 2000.

--
Regards,
Tom Ogilvy

keepitcool wrote in message
...
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


"Jason" wrote:

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

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.

--
Regards,
Rocky McKinley


"Tom Ogilvy" wrote in message
...
He is copying sheets within a single workbook, not copying workbooks.

--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
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.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
That is in Excel 97 only. Believe it was fixed in Excel 2000.

--
Regards,
Tom Ogilvy

keepitcool wrote in message
...
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


"Jason" wrote:

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