Save One sheet

[Guest]

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.

 

[Chip Pearson]

You can use the Copy method of the Worksheet object to copy a
single sheet to a new workbook.

Worksheets("Sheet1").Copy

Once this line of code is executed, the new workbook will be the
ActiveWorkbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


in message
news:[email protected]...

 

[George Nicholson]

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 Before:=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,

 

[Myrna Larson]

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.

 

[George Nicholson]

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