Copy Method of Worksheet Fails

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
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 :
Code:
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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 :
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top