Save One sheet
 

Manually:
-Right click on the sheet tab. Select "Move or copy..."
-Under "To book" select "(new book)". Check "Create a copy". Click OK
-Save the new book with whatever name you choose.

Via code:
ThisWorkbook.Sheets("MySheet").Copy
(If you use this method without specifying a Before or After argument, the
sheet will be copied to a new 1-sheet workbook. Now you have to find that
new workbook in order to save it. I'm assuming there will only be one "new"
book at this point and that your other open workbooks names don't start with
"Book" - a lot of assumptions.)
For Each wkb In Workbooks
If Left(wkb.Name, 4) = "Book" Then
wkb.SaveAs "MyNewBook.xls"
Exit Sub
End If
Next wkb

Alternatively, use Workbooks.Add 1st to create a new workbook (provides a
handle for the new book from the start) and then copy the sheet into it:

Set wkb = Workbooks.Add
ThisWorkbook.Sheets("MySheet").Copy Befo=wkb.Sheets(1)

Application.DisplayAlerts = False
For i = wkb.Sheets.Count To 2 Step -1
' Delete all but the 1st sheet
wkb.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

wkb.SaveAs "MyNewBook.xls"

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Spencer Hutton" wrote in message
...
is it possible to save just one sheet of a workbook in it's own new
workbook?
i have a workbook with 3 sheets in it. how can i copy sheet1 to a new
workbook, and save it under the filename "savedbook". TIA.

Save One sheet
 

You don't need to do all of that work to "find" it. It's the active workbook
at this point, as Chip Pearson points out in his reply.


On Fri, 18 Feb 2005 13:40:25 -0600, "George Nicholson"
wrote:

(If you use this method without specifying a Before or After argument, the
sheet will be copied to a new 1-sheet workbook. Now you have to find that
new workbook in order to save it.

Save One sheet
 

Sorry Myrna, I don't like making assumptions like that, especially since it
isn't (to my knowledge) a documented "feature" of that method and I try to
avoid code that depends on something maintaining its "activeness".

I guess I'm just not that trusting :-)

--
George Nicholson

Remove 'Junk' from return address.


"Myrna Larson" wrote in message
...
You don't need to do all of that work to "find" it. It's the active
workbook
at this point, as Chip Pearson points out in his reply.


On Fri, 18 Feb 2005 13:40:25 -0600, "George Nicholson"

wrote:

(If you use this method without specifying a Before or After argument, the
sheet will be copied to a new 1-sheet workbook. Now you have to find that
new workbook in order to save it.