Nsryjdtyk

This code takes, on average, 6 minutes to fully run with the workbook I am using. The workbook currently has 148 worksheets and will grow consistently with time.

I am curious if there is a way to edit the code from doing the task one worksheet at a time; and, instead complete the task all in one moment. In my mind, the code would run for a much shorter time and then populate all the worksheets as separate workbooks in the file destination at the same time. The worksheets would also not "pop-up" before saving themselves in the destination folder. Much like stopping screen updating and automatic calculations making a world of difference - except that did not work with this code.

Sub SaveFilesInFolder()

'

'Macro for saving each worksheet as a workbook in a destination folder

'



'

Dim sh As Worksheet

Dim wb As Workbook



For Each sh In Worksheets

    SheetName = sh.Name

    sh.Copy



        With ActiveWorkbook

            .SaveAs FileName:="FolderDestination" & SheetName

            .Close SaveChanges:=True

        End With



    Next sh



End Sub

Any suggestions are appreciated.

What you're looking for is to turn off screenupdating, turn off alerts and auto-save and close.

    Application.DisplayAlerts = False

    ThisWorkbook.Save

    ThisWorkbook.Close False

So, like this -

Option Explicit



Sub SplitSheetsToBooks()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Const PATH_TO As String = "C:TEMP"

    Const EXCEL_EXTENTION As String = ".xlsx"



    Dim index As Long

    Dim numberOfSheets As Long

    Dim targetSheet As Worksheet

    numberOfSheets = ThisWorkbook.Sheets.Count



    For index = 1 To numberOfSheets

        Set targetSheet = ThisWorkbook.Sheets(index)

        targetSheet.Copy

        ActiveWorkbook.SaveAs PATH_TO & targetSheet.Name & EXCEL_EXTENTION

        ActiveWorkbook.Close False

    Next

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub