Saving only 1 sheet of a workbook
 

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
---
Jay Wilson

Saving only 1 sheet of a workbook
 

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)

"Jay Wilson" wrote in message
...
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
---
Jay Wilson

Saving only 1 sheet of a workbook
 

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

--
Regards,
Tom Ogilvy

"Jay Wilson" wrote in message
...
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
---
Jay Wilson

Saving only 1 sheet of a workbook
 

Bob

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

Thanks
---
Jay Wilson


On Mon, 2 Feb 2004 16:04:33 -0000, Bob Phillips wrote:

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

Saving only 1 sheet of a workbook
 

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)

"Jay Wilson" wrote in message
...
Bob

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

Thanks
---
Jay Wilson


On Mon, 2 Feb 2004 16:04:33 -0000, Bob Phillips wrote:

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