Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save a Sheet as a Workbook (2000)

    I need to select a sheet from a workbook, then save that one sheet into a new book.

    I am able to do it by copying the sheet then pasting it into a new book, but I was wondering if there was a shortcut that didn't open all these new windows and such. Thanks.

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a Sheet as a Workbook (2000)

    OK, I think I can just use Application.Activesheet.SaveAs() to do this. However, I need to bring up the SaveAs dialog box so the user can input his own filename/path. I know to do this for the whole workbook I can say

    Application.Dialogs(xlDialogSaveAs).Show

    But how can I do this when saving only one sheet to a new book?

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a Sheet as a Workbook (2000)

    Did you try Edit -> Move or Copy Sheet... ?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a Sheet as a Workbook (2000)

    If you could use the SaveAs method, then you would have to use the GetOpenFilename method to allow the user to specify a file name, then use that filename with the SaveAs method. Unfortunately, this is not going to work for you. When you use SaveAs, Excel will save the entire workbook, even though you specify a single worksheet as the object. I think that you are either going to have to do what you are currently doing, or change the code to delete all sheets in the workbook before doing the SaveAs.
    Legare Coleman

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save a Sheet as a Workbook (2000)

    Try the following :<pre>Sub SheetSave()
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs (<font color=red>Path Filename</font color=red>)
    ActiveWorkbook.Close
    End Sub</pre>

    If you do not want to retain a copy of the sheet in the original workbook, replace ActiveSheet.Copy with ActiveSheet.Move.

    Replace the red text with the Path and Filename as required.

    Andrew C

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save a Sheet as a Workbook (2000)

    Sorry, but I did not properly read your supplementary post. Try this amended code which prompts for the filename.<pre>Sub SheetSave()
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs (Application.GetSaveAsFilename)
    ActiveWorkbook.Close
    End Sub</pre>

    Andrew C

Posting Permissions

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