Copy Method of Worksheet Fails

[Nimrod]

Hello fellow xls'rs:
I have a workbook that has a long list of sheetnames in sheet1!A:A. In this same workbook I have a Master Sheet . I have a procedure that copies the mastersheet and then renames it.(using the list of names from sheet1!A:A.) This procedure works quite well until about the 39 loop and all of a sudden gives me a RunTime error 1004 and says "copy method of worksheet class failed"

The line it fails on is this :

Worksheets(SalesMasterSheet).Copy after:=Worksheets(LastMadeSheet)

I've changed the order of the sheetnames in Sheet1!A:A and it still fails on the same loop ie. 39 , so it's not a typo in the file name.

I've also run the procedure on different PC's with same result..... Any ideas ?

[ This Message was edited by: nimrod on 2002-05-10 18:16 ]

[Ivan F Moala]

On 2002-05-10 18:00, Nimrod wrote:
Hello fellow xls'rs:
I have a workbook that has a long list of sheetnames in sheet1!A:A. In this same workbook I have a Master Sheet . I have a procedure that copies the mastersheet and then renames it.(using the list of names from sheet1!A:A.) This procedure works quite well until about the 39 loop and all of a sudden gives me a RunTime error 1004 and says "copy method of worksheet class failed"

The line it fails on is this :

Worksheets(SalesMasterSheet).Copy after:=Worksheets(LastMadeSheet)

I've changed the order of the sheetnames in Sheet1!A:A and it still fails on the same loop ie. 39 , so it's not a typo in the file name.

I've also run the procedure on different PC's with same result..... Any ideas ?

[ This Message was edited by: nimrod on 2002-05-10 18:16 ]

Hi Nimrod
Excel97 ? - I beleive this is a known problem
in copying sheets ie
Sheets("master...").Copy

Can't find the Ms Link but I beleive the work
around is to add a New sheet & do a cells.copy & Paste to New sheet.

[Ivan F Moala]

On 2002-05-10 18:38, xl wrote:
Sucks to be you.

A helpful suggestion would be nicer

[Jay Petrulis]

On 2002-05-10 18:38, xl wrote:
Sucks to be you.

Amazing. You are trolling on a moderated board.

Nimrod helps others all the time. S/he just responded to a post in Spanish! So, when Nimrod needs some assistance, this is your offering. Good job. Really impressive.

Believe it or not, you can learn a lot here. Yet, I would imagine that not many people will offer any Excel help to you, should you ever need it. Quite a shame, actually.

[Nimrod]

Thanks for the Comments Jay and Ivan. I do enjoy this board and the people on it. Everyone seems to be very supportive of one another.

[Nimrod]

Thanks for the suggestion Ivan . I'm actually using Office 2000 and usually keep up with the SP's! I'm going to try your suggestions and see where it takes me ... again thanks for the help.

[Tom Schreiner]

Shot in the dark. Has worked before for other "method" errors.

Place this:

Worksheets(SalesMasterSheet).Range("A1").Activate

Before your line of code:

Worksheets(SalesMasterSheet).Copy after:=Worksheets(LastMadeSheet)

Tom

[Nimrod]

Nope TS ....... that one didn't work :-| ...hmmm I'm going to keep trying

[Nimrod]

Hi Ivan :
Your idea of copy and paste was good but unfortunately I loose all the page setup information and to set that stuff up through the macro slows everything considerably.
Any other ideas ?

[Ivan F Moala]

On 2002-05-10 21:03, Nimrod wrote:
Hi Ivan :
Your idea of copy and paste was good but unfortunately I loose all the page setup information and to set that stuff up through the macro slows everything considerably.
Any other ideas ?

When setting up each sheet use
ActiveSheet.DisplayAutomaticPageBreaks = False

This speeds up the setup process...