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
Leave a Reply