Export only one Sheet without deleting the others?

[WillyTheFish]

Hey guys,

this is probably an easy one for you, but I couldn't find any solution.
I have a Workbook containing lets say 8 Sheets and only want
to export/save one of those sheets in a separate workbook, without having to delete the other sheets and then save the current workbook under another name =P

Any suggestions? Thanks :)

[mdmackillop]

Sub MakeNew()
ActiveSheet.Copy
ActiveWorkbook.SaveAs InputBox("Enter path and filename")
ActiveWorkbook.Close
End Sub

[GTO]

Howdy,

Just in case you don't "trust" the user to enter the fullname, another way...

hope this helps,

Mark

Sub SaveASheetAs()

'// Copy the active sheet to a new, one-sheet wb//
ActiveSheet.Copy

'// Change the directory (folder) to where your workbook is (or change //
'// to wherever you want.//
ChDir ThisWorkbook.Path

'// Display SaveAs application dialog box//
Application.Dialogs(xlDialogSaveAs).Show
'// Presuming you saved the copy of the sheet as a new workbook, close it //
'// and return to your 'master' workbook. If you cancelled the save, the //
'// sub will just exit. //
If Not ActiveWorkbook.Name = ThisWorkbook.Name _
Then ActiveWorkbook.Close False

End Sub