Results 1 to 5 of 5

Thread: Excel, save just a single worksheet to new XLS file [resolved]

Hybrid View

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422

    Question Excel, save just a single worksheet to new XLS file [resolved]

    How can I, from within the VBA code, save a single worksheet to a new .XLS file?

    I tried the sheet.SaveAs filename method
    but it saves the ENTIRE current file, all sheets and macros etc, into the new file.

    I just want an XLS file with ONE sheet.

    Here's what I'm doing now
    Code:
        Dim NewSheet As Worksheet
        Dim NewSheetName As String
    
        ' Copy the original, target sheet to temp so we can operate on it
        Worksheets(Original_Sheet_Name).Copy after:=Sheets(Sheets.Count)
        Set NewSheet = Worksheets(ActiveSheet.Name)
        NewSheet.Name = NewSheetName
           (do stuff ...)
        NewSheet.SaveAs FileName
    Thanks, DaveBo
    Last edited by DaveBo; Apr 27th, 2004 at 10:37 AM.
    "The wise man doesn't know all the answers, but he knows where to find them."
    VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15

  2. #2
    PowerPoster Dave Sell's Avatar
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    2,961
    I would just delete all the unwanted sheets out of the workbook then save it; it will have only the one sheet in it.

    If you need to keep those other sheets, then obviouosly create a new workbook for the sheet you want to keep, setting the workbook to open with only 1 sheet in it.

    That help?

    Dave

  3. #3
    Lively Member TheFIDDLER's Avatar
    Join Date
    May 2002
    Location
    here and there and far away
    Posts
    126
    Here are three simple lines of code that go a long way in what you are trying to accomplish.

    Here is what I use.

    VB Code:
    1. Sub movesheet2newbook()
    2.     Application.ScreenUpdating = False
    3.     Sheets("test").Copy
    4.     ActiveWorkbook.Close savechanges:=True, FileName:="C:\onesheet.xls"
    5. End Sub

    Works since the default action of copying sheets is to paste to a new workbook. It will only send what you require. Then we close out of the new workbook and with screen updating set to false, no one is the wiser that you just moved from one book to the other, and you finish in your original book.

    For multiple sheets, use the following:
    VB Code:
    1. Sub movesheets2newbook()
    2.      Application.ScreenUpdating = False
    3.      Sheets(Array(Sheetname, Sheetname2, Sheetname3)).Copy
    4.     ActiveWorkbook.Close savechanges:=True, FileName:="C:\threesheets.xls"
    5. End Sub
    -----
    #VBA, VB 6 Professional Edition, Office XP Developper. Excel 97, Excel 2000, Excel XP

    I miss my VIC 20.
    Never should have upgraded to my commodore 64. ...

  4. #4
    Addicted Member
    Join Date
    Dec 2001
    Posts
    158

    Thumbs up

    Yep.


    BTW: An easy way to figure out stuff like this is to start recording a macro and then do the action (right click on "mysheet", and select "new workbook" with "copy" checked).

    On a lot of stuff I do, sometimes it's just easier to record a macro to get a code section instead of typing it out by hand.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422

    Thanks all.

    What I wound up doing was creating a new workbook (WorkBooks ... .Add)
    copying the sheet there, doing the sheet processing, then saving the new temp workbook.

    The original workbook & sheet has to remain unchanged.

    Thanks, DaveBo
    "The wise man doesn't know all the answers, but he knows where to find them."
    VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15

Posting Permissions

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



Click Here to Expand Forum to Full Width