Create a new Workbook for each Worksheet
Sub ExportWorksheetAsWorkbook()
Dim XSheet As Worksheet
Dim XBook As Workbook
Dim XPath As String
XPath = "F:\Sales Rep\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each XSheet In ActiveWorkbook.Sheets
Set XBook = Workbooks.Add
XSheet.Copy After:=XBook.Sheets(XBook.Sheets.Count)
XBook.Worksheets("Sheet1").Delete
XBook.SaveAs XPath & XSheet.Name & ".xlsx"
XBook.Close
Next XSheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Note: “F:\Sales Rep\” is the path of the destination folder and can be replaced by the folder path of your choice.
Copy the Activesheet to a New workbook
Sub CopyWorksheet() ThisWorkbook.ActiveSheet.Copy Before:=Workbooks.Add.Worksheets(1) End Sub