+ Reply to Thread
Results 1 to 4 of 4

Problem with SaveAs (Run-time error '1004")

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    82

    Problem with SaveAs (Run-time error '1004")

    Hi all,
    I am having issues with Codes.
    Sometimes it work, sometimes it doesn't.
    What I am trying to do is to save a copy of my existing worksheet to another file, I would also need to remove some sheets on that new copy.


    Here is my code:

    Please Login or Register  to view this content.
    I am getting an error:
    Run-time error '1004"
    Application-defined or Object-defined error

    On line:
    ActiveWindow.SelectedSheets.Delete

    Any ideas why?
    Better yet, would you be able to recommend a better way of doing this?
    Appreciate any help. Thank you.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,013

    Re: Problem with SaveAs (Run-time error '1004")

    Dealing with things by identifying what is active and selected can be error-prone. From your description, I gather that you want to do all this work on your copy. I would try this using explicit identifiers for the workbook and sheets. You also don't have to turn off alerts more than once. I have compiled but not tested this.
    Please Login or Register  to view this content.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Problem with SaveAs (Run-time error '1004")

    As mentioned its generally best practice to explicitly reference objects. Working with relative objects like active and selected is often difficult to troubleshoot.

    Also, there are various reasons you may not be able to delete a sheet and I do not see any checks for these types of things. For example sheet protection. If any of the selected sheets are protected it would fail or if the workbook structure is protected. If the workbook is in shared mode this may fail as well (unconfirmed). It would likely fail if a cell on a selected sheet was in edit mode (ex: while entering content within the cell and not having completed entry).

    You should review the file(s) you are dealing with and check for things like protection and other special status's that would prevent deletion.

  4. #4
    Registered User
    Join Date
    10-13-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: Problem with SaveAs (Run-time error '1004")

    Quote Originally Posted by 6StringJazzer View Post
    Dealing with things by identifying what is active and selected can be error-prone. From your description, I gather that you want to do all this work on your copy. I would try this using explicit identifiers for the workbook and sheets. You also don't have to turn off alerts more than once. I have compiled but not tested this.
    Thank you Jeff but no dice. I had the same error but on this line: NewCopy.Sheets("ControlPanel").Delete

    And to answer ZeroCool, all my sheets are unprotected. Data is not shared, I am the only person using it and it will not be shared over the network or something like that.
    None of the cells on the sheets to be deleted are on edit mode as well.

    Appreciate your guidance. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] run into 1004 error "method 'saveas' of object '_workbook' failed" when saving wb copy
    By tadaw in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-10-2017, 06:18 AM
  2. run time error 1004 method saveas of object _workbook failed (help)
    By Cbirchfi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2016, 04:03 PM
  3. Replies: 8
    Last Post: 02-19-2014, 09:55 AM
  4. getting run time error 424 or 1004 when using Saveas method
    By Anchal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2013, 03:37 AM
  5. SAVEAS problem, "Error 1004".
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2012, 08:09 AM
  6. Run Time Error '1004': Method 'saveas' of object '_workbook' failed
    By vcuphysics in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2012, 04:50 PM
  7. Basic "run-time error 1004" problem
    By hoyyqs012 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-31-2009, 12:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1