Consulting

Results 1 to 7 of 7

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

  1. #1

    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    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
    Hi Aflatoon:

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

    ActiveWorkbook.SaveAs "C:\GraphicSheetTest.xls", FileFormat:=56 
    
    
    Formatting tags added by mark007
    The line that causing the problem is the Copy portion of the code;
    Sheets(Array("NewMODSheet", "Sheet2")).Copy 
    
    
    Formatting tags added by mark007
    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,485
    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
    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,485
    Location
    No issues with either of these:
    ActiveWorkbook.SaveAs "C:\VBAX\GraphicSheetTest.xlsm", FileFormat:=52 
    ActiveWorkbook.SaveAs "C:\VBAX\GraphicSheetTest.xls", FileFormat:=56 
    
    
    Formatting tags added by mark007
    As a last resort try
    Worksheets(Array("NewMODSheet", "Sheet2")).Copy 
    
    
    Formatting tags added by mark007
    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
    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
  •