Tom’s Tutorials For Excel: Save Each Worksheet As Its Own Workbook

Tom’s Tutorials For Excel: Save Each Worksheet As Its Own Workbook

For the active workbook, this macro saves each visible worksheet as its own workbook in the current path. Workbooks are named as their sheet tab name, example, Sheet1’s workbook name would be “Sheet1.xls”.

Sub SaveWorksheetsAsWorkbooks()
Application.ScreenUpdating = False
Dim ws As Worksheet, strPath$, strSheetName$
strPath = ThisWorkbook.Path & "\"
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
strSheetName = ws.Name
ws.Copy
ActiveWorkbook.SaveAs strPath & strSheetName & ".xls"
ActiveWorkbook.Close 0
End If
Next ws
Application.ScreenUpdating = True
MsgBox "Your sheets are individually saved in the path" & vbCrLf & _
strPath, 64, "Done"
End Sub