Consulting

Results 1 to 7 of 7

Thread: Method 'Copy' of object'_Worksheet' failed PROBLEM

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    314
    Location

    Method 'Copy' of object'_Worksheet' failed PROBLEM

    Trying to create a new workbook but the Method 'Copy' of object'_Worksheet' failed error keeps preventing the copy process.
    I've attached a workbook. The Macro is run from the NewMODSheet WorkSheet.

    The Button says " Add A New WorkBook"

    Caution! The attached worksheet crashes when the Macro runs.
    But at the bottom of the sheet it shows copying.
    A new Book1 is created, but it's hidden from view, and the worksheet crashes.

    The same code works on other sheets.
    Attached Files Attached Files

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    Not far
    Posts
    1,526
    It doesn't crash for me, though of course you will get prompted about removing the VB project as you are trying to save a workbook with code in it as an xlsx file. Also, you really ought to specify the file format parameter.
    Be as you wish to seem

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    314
    Location
    Hi Aflatoon:

    I have tried to save in every format I can think of, including

    ActiveWorkbook.SaveAs "C:\GraphicSheetTest.xls", FileFormat:=56
    The line that causing the problem is the Copy portion of the code;
    Sheets(Array("NewMODSheet", "Sheet2")).Copy
    I'm still not sure what's the problem.

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,488
    Location
    Newer versions of Windows will not allow you to save a file to the Root directory. Try another folder.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    314
    Location
    mdmackillop:

    I have already tried that.
    ActiveWorkbook.SaveAs "C:\Daily\GraphicSheetTest.xls", FileFormat:=56

    Same exact error at the same location. Were you able to get it to save?

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,488
    Location
    No issues with either of these:
    ActiveWorkbook.SaveAs "C:\VBAX\GraphicSheetTest.xlsm", FileFormat:=52
    ActiveWorkbook.SaveAs "C:\VBAX\GraphicSheetTest.xls", FileFormat:=56
    As a last resort try
    Worksheets(Array("NewMODSheet", "Sheet2")).Copy
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    314
    Location
    Thanks mdmackillop;

    I was away from Computers for a while. Traveling & A minor medical issue. Now, back to work.

Posting Permissions

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