save worksheet as new workbook

[dreamer]

Is there a way to save just one worksheet as a new workbook

 

[Bob Phillips]

Not directly. You could loop round and delete all the others except, and the
SaveAs.

Application.DisplayAlerts = False
For Each sh In Activeworkbook
If sh.name <> "mySheet" Then
sh.Delete
End If
Next Sh
Application.DisplayAlerts = True

--

HTH

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

 

[Nick Cranham]

dreamer,
Right click on that sheet's tab, select "Move or Copy", select "New Book",
check "Create a copy" if desired. Voila.
If you run the macro recorder whilst you do that, you'll have a good idea of
the code required.

(Assuming you're not trying to do this with a file from a mixed language
network: See the thread ""Path/File Access Error" on Sheets.Copy" below.)

NickHK

 

[srgggf]

Not directly. You could loop round and delete all the others except, and the
SaveAs.

Activesheet.copy would appear a pretty direct means of generating a new
workbook containing (only) the desired sheet.

 

[Bob Phillips]

But that is still not directly

Bob

Activesheet.copy would appear a pretty direct means of generating a new
workbook containing (only) the desired sheet.

 

[srgggf]

Hi Bob,

Indeed the Activesheet.Copy command directly creates a new single-sheet
workbook.

I suspect that you have confused this direct creation with a subsequent
(optional) save.

In any event, the looping deletion of other sheets method which you suggest
seems inappropiately lengthy in operation and overly verbose code.
Certainly, were I dealing with (say) a 100-sheet primary workbook, I know
which approach I would adopt.

Just my opinion of course.

 

[Bob Phillips]

I know what Activesheet.Copy does, but direct in my parlance would be
something like

SaveSheetAs filename

which doesn't exist. Deleting may be more lengthy, but both methods have to
create the target before saving. Hence, neither is direct, so guess what, I
am not confusion direct with a subsequent save.

--

HTH

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