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

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

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:

Sub movesheet2newbook()
    Application.ScreenUpdating = False
    Sheets("test").Copy
    ActiveWorkbook.Close savechanges:=True, FileName:="C:\onesheet.xls"
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:

Sub movesheets2newbook()
     Application.ScreenUpdating = False
     Sheets(Array(Sheetname, Sheetname2, Sheetname3)).Copy
    ActiveWorkbook.Close savechanges:=True, FileName:="C:\threesheets.xls"
End Sub

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.

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