Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Copy Method of Worksheet Fails

  1. #1
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-10 18:38, xl wrote:
    Sucks to be you.
    A helpful suggestion would be nicer
    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...



    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •