Saving only 1 sheet of a workbook

J

Jay Wilson

How do I save only 1 sheet of a workbook into a new file

I tried
Worksheets("Save Sheet").Saveas Filename:="save.xls"

The above for some reason is saving all sheets and macros, not just the one
I requested it save.

Thanks
 
B

Bob Phillips

Jay,

One way

Application.DisplayALerts = False

For each sh in Activeworkbok.sheets
if sh.name <> "Save Sheet" Then
sh.delete
end If
Next sh

Activeworkbook.Saveas Filename:="save.xls

Application.DisplaAlerts = True

--

HTH

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

Tom Ogilvy

worksheets("Save Sheet").Copy ' copies to a new workbook
Activeworkbook.SaveAs "Save.xls"
 
J

Jay Wilson

Bob

Will this snippet of code also remove the macros? I don't want them in the
saved copy.

Thanks
 
B

Bob Phillips

No, it will only remove any code in the sheets not being saved. To remove
code, use
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

You will need to set a reference to the Microsoft Visual Basic For
Applications Extensibility.

--

HTH


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