Save only one sheet of workbook with vba statement

  • Thread starter Thread starter Pmxgs
  • Start date Start date
P

Pmxgs

Hi!
I have a workbook with four sheets, and i'm writing a procedure where at
some point i need to save only one of the worksheets as a new excel file
(workbook).
i used:
supose i want to save "sheet2"
worksheets("sheet2").saveas(filename)

This works well except the new file contains all of the sheets of the
original workbook.

Thanks,

pmxgs
 
Go to Tools > Macro >Record New Macro >This Workbook or
Personal Workbook. Then highlight sheets 1, 3 and 4 then
delete. Go to Tools > Macro > Stop Recording. Then, copy
the macro commands.
 
Buyt i want to save each sheet into a new workbook, which contains only one
sheet.

--------------------------------
 
This might get you started:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook '???

For Each wks In wkbk.Worksheets
wks.Copy 'to a new workbook
With ActiveWorkbook
.SaveAs Filename:=wkbk.Path & "\" & wks.Name, FileFormat:=xlNormal
.Close savechanges:=False
End With
Next wks

End Sub



I saved the new workbooks using the names of each worksheet.
 
thanks



Dave Peterson said:
This might get you started:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook '???

For Each wks In wkbk.Worksheets
wks.Copy 'to a new workbook
With ActiveWorkbook
.SaveAs Filename:=wkbk.Path & "\" & wks.Name, FileFormat:=xlNormal
.Close savechanges:=False
End With
Next wks

End Sub



I saved the new workbooks using the names of each worksheet.
Click to expand...
 
Back
Top