Sub SplitAllWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Dim ws As Worksheet Dim newWorkbook As Workbook For Each ws In ThisWorkbook.Sheets Set newWorkbook = Workbooks.Add ' Create a new workbook ' Copy data and formatting to the new workbook ws.Cells.Copy newWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteAll Application.CutCopyMode = False ' Save and close the new workbook with the sheet's name newWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" newWorkbook.Close False Next ws Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub