none
Error 1004 while saving single sheet as xls RRS feed

  • Question

  • My code should copy a single sheet from the active workbook to a new workbook and then save this new workbook as a xls. I am trying to use the code I found on the web, which seem to be exactly it, but at the saving step the error 1004 occurs. Would anyone knows what is wrong and how to make it work? The code goes below:

    Sub movesheet2newbook()
        Application.ScreenUpdating = False
        Sheets("Sheet2").Copy
        ActiveWorkbook.Close savechanges:=True, Filename:="C:\onesheet.xls"
    End Sub


    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Friday, January 9, 2015 9:39 PM

Answers

  • Hello Jorge,

    You can't just save to the path Desktop. You need the fully qualified path like below. Your path is unlikely to be the same as the example so you will need to identify the actual path to which you want to save.

    Sub movesheet2newbook()

        Application.ScreenUpdating = False
        Sheets("Sheet2").Copy
        ActiveWorkbook.SaveAs Filename:= "C:\Users\User\Desktop\onesheet.xls", FileFormat:=xlExcel8
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
    End Sub

    The following example saves the new workbook to the same path as the workbook containing the VBA code. Note the leading backslash added to the filename.

    Sub movesheet2newbook()
        Application.ScreenUpdating = False
        Sheets("Sheet2").Copy
        ActiveWorkbook.SaveAs Filename:= _
            ThisWorkbook.Path & "\onesheet.xls", FileFormat:=xlExcel8
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
    End Sub


    Regards, OssieMac

    Saturday, January 10, 2015 6:53 AM

All replies

  • In recent versions of Windows, saving to the root of the system disk C:\ is usually not allowed, so I'd use a different path.

    Also, I'd do it like this to avoid conflicts over the file type:

    Sub movesheet2newbook()
        Application.ScreenUpdating = False
        Sheets("Sheet2").Copy
        ActiveWorkbook.SaveAs Filename:="C:\Excel\onesheet.xls", FileFormat:=xlExcel8
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 9, 2015 9:58 PM
  • Hello once again Hans,

    I copied your code and tried, the same error occurred. I changed the directory to Desktop and the same 1004 error keeps occurring. Can you figure it out?


    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Saturday, January 10, 2015 1:08 AM
  • Hans,

    By the way, I just realized that this same error occurs if I create a 1 line sub with the saveas method, such as:

    Sub subSavingmMe()
    Me.SaveAs ("C:\Users\jmartins\Documents\Book111.xlsx")
    End Sub

    So, there is something wrong with this SaveAs Method.


    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Saturday, January 10, 2015 1:44 AM
  • Hello Jorge,

    You can't just save to the path Desktop. You need the fully qualified path like below. Your path is unlikely to be the same as the example so you will need to identify the actual path to which you want to save.

    Sub movesheet2newbook()

        Application.ScreenUpdating = False
        Sheets("Sheet2").Copy
        ActiveWorkbook.SaveAs Filename:= "C:\Users\User\Desktop\onesheet.xls", FileFormat:=xlExcel8
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
    End Sub

    The following example saves the new workbook to the same path as the workbook containing the VBA code. Note the leading backslash added to the filename.

    Sub movesheet2newbook()
        Application.ScreenUpdating = False
        Sheets("Sheet2").Copy
        ActiveWorkbook.SaveAs Filename:= _
            ThisWorkbook.Path & "\onesheet.xls", FileFormat:=xlExcel8
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
    End Sub


    Regards, OssieMac

    Saturday, January 10, 2015 6:53 AM
  • Hello OssieMac,

    What you said makes sense, but I tried and the same Run-time error 1004 (Application-defined or object-defined error) keeps occurring as you may see on the picture. Any other idea?

    As I replied to Hans, this error is occurring everytime I use a SaveAs statement. 


    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Saturday, January 10, 2015 11:20 AM
  • Your picture indicates that you simply used the same path to Desktop that I posted in the example. It is highly unlikely that your path to the desktop folder is the same as mine. I have called my name on the computer user and this part of the path will need to be changed to the name allocated on your computer.

    Try the second example that I posted and see if it will save to the same folder as the one containing the VBA code.

    Alternatively in your sub called teste insert Stop on the line below the MsgBox line and when it stops hover the cursor over the variable strDirectory and that is the path to use in the SaveAs but you will need to concatenate the last backslash and file name to it.


    Regards, OssieMac

    Saturday, January 10, 2015 11:42 AM
  • OssieMac,

    It worked man! You were right, the problem was the directory. By the way, I am looking for a book to master myself in VBA, would you suggest me any?

    Thank you very much!

    Jorge Barbi Martins


    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Saturday, January 10, 2015 1:35 PM
  • By the way, I am looking for a book to master myself in VBA, would you suggest me any?

    In my part of the world we have free public libraries and in the past I have borrowed various books. I don't really have an opinion on which is the best because I learnt stuff from most of them so if you have access to a library then try there first and see what books suit you. Alternatively try book shops and peruse what they have.

    There are also a number of on line tutorials. Just Google  "Excel VBA tutorial".

    With the problem you have posted here you should have been able to sort it by turning on the VBA recorder and you could have recorded the code while you performed the operation in the interactive mode.


    Regards, OssieMac

    Saturday, January 10, 2015 8:08 PM
  • I like the Excel and Excel VBA books by John Walkenbach.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, January 10, 2015 8:44 PM
  • Just turn on the Macro recorder before you save the Workbook.  Then, simply save it.  Check your code.  You'll have exactly what you need.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, January 10, 2015 9:46 PM
  • I'm always trying to figure things out by using the macro recorder and reading the code; however, I'm not sure but I guess there is no way to manually save just a single sheet of a workbook as a new workbook. Anyways, it's solved. Thanks once again OssieMac. Jorge

    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Monday, January 12, 2015 2:19 AM
  • I'm gonna study it then. Thanks Hans.

    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Monday, January 12, 2015 2:21 AM
  • Thanks Ryguy72

    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Monday, January 12, 2015 2:22 AM
  • I'm not sure but I guess there is no way to manually save just a single sheet of a workbook as a new workbook.

    Easy.

    If you want the code then turn on the Macro recorder first.

    Right click the tab name of the worksheet to be saved.

    Select Move or copy.

    Click the DropDown under "To Book" and select (New Book)

    Check the box against Create Copy (This is important or the sheet gets moved to new workbook instead of copied)

    Click OK. The new workbook is now the active workbook.

    Save the active workbook and close. (After closing the old workbook becomes the active workbook)

    Turn off the macro recorder. The code will be in the original (old) workbook


    Regards, OssieMac



    • Edited by OssieMac Monday, January 12, 2015 4:23 AM
    Monday, January 12, 2015 4:17 AM
  • Once again your are right. Thank you. Jorge

    Jorge Barbi Martins (jorge.barbi@hotmail.com)

    Monday, January 12, 2015 12:23 PM