Method exposed by Sheet/Worksheet object which copy the entire sheet with its structure and formatting. In this article we will copy each sheet and insert into a new workbook and save workbook with sheet name to a common location.
expression.Copy Before, After
Public Sub SaveEachTabIntoFile(fileLocation As String) 'Check validate file path If fileLocation <> "" Then 'Declare worksheet object Dim oWorkSheet As Worksheet 'Declare workbook object Dim oWorkBook As Workbook 'Bind active workbook reference Set oWorkBook = ActiveWorkbook 'Iterate each sheet from current workbook For Each oWorkSheet In oWorkBook.Worksheets 'Declare new workbook object Dim oTargetWorkBook As Workbook 'add workbook and bind reference Set oTargetWorkBook = Workbooks.Add 'Copy sheet oWorkSheet.Copy Before:=oTargetWorkBook.Worksheets(1) 'Save file wth sheet name oTargetWorkBook.SaveAs fileLocation & "\" & oWorkSheet.Name & ".xlsx" 'Close workbook oTargetWorkBook.Close 'Memory cleanup Set oTargetWorkBook = Nothing Next oWorkSheet 'Memory cleanup Set oWorkSheet = Nothing Set oWorkBook = Nothing End If End Sub
Sub SaveAllSheets() 'Call method Call SaveEachTabIntoFile("C:\Users\Downloads") End Sub