Macro to save sheets as separate workbooks

Z

Zorro

can anyone help with a macro to save each worksheet as a separate book
(named as its sheet name) to a given folder (C:\mydocs\myfolder)?

Many thanks
Zorro
 
D

Dave Peterson

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

For Each wks In activeworkbook.worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\mydocs\myfolder\" & .Name, _
FileFormat:=xlworkbooknormal
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
Next wks

End Sub

It also overwrites any existing file (if one exists).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top