Saving a worksheet out of a workbook

[Fred Holmes]

Excel 2000

Is there a programming statement that will save a worksheet out of a
workbook to a new filename?

Application.ActiveWorkbook.Sheet4.SaveCopyAs ("Coffee-A.xls")

The foregoing statement fails because the SaveCopyAs method only
supports worksbooks, not worksheets.

Thanks for any help.

Fred Holmes

 

[George Nicholson]

Check out the Worksheet.Copy method. Note that "...If you don't specify
either Before or After, Microsoft Excel creates a new workbook that contains
the copied sheet." (and ONLY the copied sheet). You would then probably
want to loop through the Workbook collection, find the new "Book x" and then
save it with a specific file name.

Alternatively, use something like (aircode)
'Create a new workbook, keeping a handle to it.
Set wkb = Workbooks.Add
' Copy a sheet to the new workbook
MyWorksheet.Copy Before:=wkb.Worksheets("Sheet 1")
' (Optionally delete all sheets in the new Workbook except the one
you just copied)
' (Do this AFTER you copy since a workbook must have at least one
sheet)
' Give the new workbook a name by saving it.
wkb.SaveAs Filename:= "MyNewFile.xls"

HTH,
George Nicholson

Remove 'Junk' from return address.

 

[Bob Phillips]

ActiveWorkbook.Sheet4.Copy
ActiveWorkbook.SaveAs Filename:="Coffee-A.xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Fred Holmes]

Marvelous. The "Copy" method is just what I was looking for.

Thanks,

Fred Holmes