Sheet to Workbook

  • Thread starter Thread starter Ed
  • Start date Start date
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?
 
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
 
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
 
Back
Top