Sheet to Workbook

E

Ed

How can i copy all sheets in a workbook to their own
workbook with the name of the sheet as the new name of the
workbook?
 
J

Jan Karel Pieterse

Hi Ed,
How can i copy all sheets in a workbook to their own
workbook with the name of the sheet as the new name of the
workbook?
Click to expand...


Option Explicit

Sub SaveAsSheet()
Dim oSh As Worksheet
Dim sPath As String
sPath = "c:\data\"
For Each oSh In ThisWorkbook.Worksheets
oSh.Copy
ActiveWorkbook.SaveAs sPath & oSh.Name
ActiveWorkbook.Close
Next
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
R

Ron de Bruin

Try this

Sub test()
Application.ScreenUpdating = False
For a = 1 To Sheets.Count
Sheets(a).Copy
ActiveWorkbook.SaveAs Sheets(1).Name
ActiveWorkbook.Close
Next a
Application.ScreenUpdating = True
End Sub
 
Top