Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Save just one sheet in a workbook

  1. #1
    Guest

    Default

    I have a workbook with so many sheets in it. I want to save just one sheet of the workbook, I mean I do not want to save the entire workbook.

    Thanks,

    Ayhan KIZILTAN

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Application.ScreenUpdating = False
    ActiveSheet.Select
    ActiveSheet.Copy
    ActiveSheet.SaveAs Filename:="YOUR DIR" & YOUR FILENAME & ".xls"
    Application.ScreenUpdating = True
    Exit Sub

    This will copy the active sheet to a new workbook with a single sheet then save it.

    Rick

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-21 08:17, RWinfield wrote:
    Application.ScreenUpdating = False
    ActiveSheet.Select
    ActiveSheet.Copy
    ActiveSheet.SaveAs Filename:="YOUR DIR" & YOUR FILENAME & ".xls"
    Application.ScreenUpdating = True
    Exit Sub

    This will copy the active sheet to a new workbook with a single sheet then save it.

    Rick
    Thanks Rick,

    I am not so good in using vba and macros. Please help me how can I make the code work, which you sendme.

    Ayhan

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub savesheet2()
    Application.ScreenUpdating = False
    ActiveSheet.Select
    ActiveSheet.Copy
    ThisFile = Range("A1").Value
    ActiveSheet.SaveAs Filename:="C:temp" & ThisFile & ".xls"
    Application.ScreenUpdating = True
    ActiveWorkbook.Close
    End Sub

    To use this open the workbook that you want to use this code in, then open the Visual Basic editor (press alt+F11). Once there click the INSERT menu item and choose MODULE from that menu. Copy the above code into the window. You need to make two changes to the above code.

    ThisFile = Range("A1").Value...change A1 to an unused cell on your worksheet. This is the cell where you will enter the file name that you want the sheet saved as.

    ActiveSheet.SaveAs Filename:="C:temp" ... change to the directory you want the file saved to.

    Once you have done this close the VB editor and the macro will appear under TOOLSMACROS menu on the workbook. If you want the macro to be called something other than savesheet2 then edit the name prior to closing the VB editor.

    Enter a file name in the cell you chose and run the macro. The macro will copy the active sheet to a new workbook with one sheet, then save the workbook as the filename you input, then will close the new workbook, which returns you to the original workbook with multiple sheets that you were working on.

    Hope this make some sense

    Rick


  5. #5
    Guest

    Default

    This may seem silly, but why not simply use the Edit / Move or Copy Sheet feature? Tick the "Copy" box, put it in a new workbook and voila, one sheet saved.

    Nanette

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Because how long do you think a macro would take to do it , repetive tasks are best undertaken by code.

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-21 12:05, Anonymous wrote:
    This may seem silly, but why not simply use the Edit / Move or Copy Sheet feature? Tick the "Copy" box, put it in a new workbook and voila, one sheet saved.

    Nanette
    The reason in the instance that I wrote the macro for was to use in a program that prints packing lists out. I have around 15 workbooks with about 200 sheets for different products and options, and this macro is actually part of another that basically prints the packing list, then saves an electronic copy of it. It reduces the file save sizes by about a factor of 10 to just save a single sheet instead of the entire workbook. The macro makes sense if you are running this particular series of workbooks on the average of 50 times a day.

    The other advantage to using a macro is that you can build in automatic error checking routines to stop duplicate file names, save a file to a particular directory without having to sort through a myriad of network folders and hopefully prevent most of the human errors that will occur with doing this process manually.

    I am assuming that the person who requested this information probably has something similar in mind.

    In a case where you are saving a single sheet occasionally then you are correct the manual method is much faster.

    Rick

  8. #8
    New Member
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-21 09:08, RWinfield wrote:
    Sub savesheet2()
    Application.ScreenUpdating = False
    ActiveSheet.Select
    ActiveSheet.Copy
    ThisFile = Range("A1").Value
    ActiveSheet.SaveAs Filename:="C:temp" & ThisFile & ".xls"
    Application.ScreenUpdating = True
    ActiveWorkbook.Close
    End Sub

    To use this open the workbook that you want to use this code in, then open the Visual Basic editor (press alt+F11). Once there click the INSERT menu item and choose MODULE from that menu. Copy the above code into the window. You need to make two changes to the above code.

    ThisFile = Range("A1").Value...change A1 to an unused cell on your worksheet. This is the cell where you will enter the file name that you want the sheet saved as.

    ActiveSheet.SaveAs Filename:="C:temp" ... change to the directory you want the file saved to.

    Once you have done this close the VB editor and the macro will appear under TOOLSMACROS menu on the workbook. If you want the macro to be called something other than savesheet2 then edit the name prior to closing the VB editor.

    Enter a file name in the cell you chose and run the macro. The macro will copy the active sheet to a new workbook with one sheet, then save the workbook as the filename you input, then will close the new workbook, which returns you to the original workbook with multiple sheets that you were working on.

    Hope this make some sense

    Rick

    Thanks Rick,

    It worked.

    Ayhan

  9. #9
    New Member
    Join Date
    Dec 2011
    Location
    North Georgia
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save just one sheet in a workbook

    How would this code change if you only wanted to copy a Range of information from the active sheet instead of the entire sheet to another file and then have the file save to the Desktop instead of C: drive??

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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