Macro to Create a Workbook for Every Worksheet

This VBA macro saves each worksheet as its own Excel file, named after the sheet, in the same folder as the workbook. Handy for sending each branch, salesperson or month their own file.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Save every sheet as a separate workbook

Sub SheetsToWorkbooks()
    Dim ws As Worksheet, p As String
    If ActiveWorkbook.Path = "" Then MsgBox "Save the workbook first.": Exit Sub
    p = ActiveWorkbook.Path & Application.PathSeparator
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs p & ws.Name & ".xlsx", xlOpenXMLWorkbook
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Done. Files saved in " & p
End Sub

ws.Copy with no destination creates a new workbook containing just that sheet. Turning off DisplayAlerts lets it replace older files with the same name.

Save every sheet as values only

To send files without formulas or links back to the original, add this line just after ws.Copy:

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Without a macro

1. Right-click a sheet tab and choose Move or Copy.
2. Under To book, choose (new book), tick Create a copy, and click OK.
3. Save the new workbook. Repeat for each sheet.

To split one sheet into many files by a column, such as region, use the split Excel file tool.

Things to know

• Sheet names become file names, so they can’t contain characters such as / or ?. Excel doesn’t allow those in sheet names anyway.
• Files are saved as .xlsx, so any macros aren’t copied. Use xlOpenXMLWorkbookMacroEnabled and .xlsm to keep them.
• Hidden sheets are saved too. Add If ws.Visible = xlSheetVisible Then inside the loop to skip them.

Related macros