Saving only 1 sheet of a workbook

[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

 

[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)

 

[Tom Ogilvy]

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

 

[Jay Wilson]

Bob

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

Thanks

 

[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)

 

[Guest]

Bob, Jay
I also have a similar problem . The solution works perfect .....but i have come across a problem that if "Trust Access to Visual Basic Project" is not checked in security options it gives error. My VBA application will be distributed to unknown audiance so it is not possible manually. Is there a wayout to do it manuall

Thanks