Export each spreadsheet to a separate workbook (Something except VBA)

Question

I have huge Excel workbook with plenty of sheets.

This is simple solution by using VBA to export each sheet to a different file (workbook).

Main issue, it is slow.

Sub Splitbook()
    MyPath = ThisWorkbook.Path

    For Each sht In ThisWorkbook.Sheets
        sht.Copy
        ActiveSheet.Cells.Copy
        ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
        ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats

        ActiveWorkbook.SaveAs _
            Filename:=MyPath & "\XXX" & sht.Name & ".xlsx"

        ActiveWorkbook.Close savechanges:=False
    Next sht
End Sub

Looking for any idea related to VBS, Python... Because based on internet research, I could not find anything related.

VBA is the quickest. Your code is slow and doing what you are doing in any other language will be even slower. — AMagpie, Mar 22 '18 at 09:15
I assume that you have to do this on regular basis (as in create new files for each sheet). If so, have you considered creating the files once for each sheet and then just reference the sheets in these files? — Zac, Mar 22 '18 at 09:16
@Zac this is how they want.... not making decisions... currently trying to find quicker way to do it. — , Mar 22 '18 at 09:20
Well you could turn of lots of things first like Application.ScreenUpdating, Calculation mode etc. SO is not really for code optimization - though you will find lots of information here for optimization. Optimization problems may be candidates for Code Review site. — QHarr, Mar 22 '18 at 09:20
@QHarr sorry miss understanding, I thought you about the way the actual VBA works. — , Mar 22 '18 at 09:24
TBH the line becomes blurred. If you search stack overflow for vba optimization or even google you will get lots of info on optimizing but with so little code it could be, not 100% sure, that you sre looking at what you can switch off whilst performing your operations and using With statements etc. http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm — QHarr, Mar 22 '18 at 09:27
@QHarr I did all of this, how I understood, nothing quicker can be used .... eh.. — , Mar 22 '18 at 09:30
I'd say setting `ScreenUpdating` to `False` alone should improve performance significantly. — Ansgar Wiechers, Mar 22 '18 at 09:31
Your code posted doesn't show that you had tried this. In an optimization problem would be a very good idea to show your optimization efforts ;-) — QHarr, Mar 22 '18 at 09:42
You can see my VBA specific answer here on optimising VBA. And a link to optimising Excel. https://stackoverflow.com/questions/26071366/how-to-speed-up-multiple-replacement-using-vba-in-word — AMagpie, Mar 25 '18 at 23:06

QHarr · Accepted Answer · 2018-03-22T19:23:17.827

So here are a few observations though:

You can switch off things that aren't required which consume resources e.g. Application.ScreenUpdating . I have borrowed a sub by @Jeeped that switches off the common items.
Use With statement
Work with only the UsedRange not the whole sheet

You might ignore this as I have ignored the Format part of your paste but have simply converted to values by setting the .Value = .Value for the conversion

Option Explicit

Public Sub Splitbook()

    Dim MyPath As String
    Dim sht As Worksheet

    appTGGL bTGGL:=False

    MyPath = ThisWorkbook.Path

    For Each sht In ThisWorkbook.Sheets

        sht.Copy

        With ActiveSheet.UsedRange
            .Value = .Value
        End With

        ActiveWorkbook.SaveAs _
        Filename:=MyPath & "\XXX" & sht.Name & ".xlsx"

        ActiveWorkbook.Close savechanges:=False

    Next sht

    appTGGL bTGGL:=True

End Sub


Public Sub appTGGL(Optional bTGGL As Boolean = True) 'https://stackoverflow.com/questions/36467663/make-the-vba-code-go-faster

    With Application
        .ScreenUpdating = bTGGL
        .EnableEvents = bTGGL
        .DisplayAlerts = bTGGL
        .Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
        .CutCopyMode = False
        .StatusBar = vbNullString
    End With

End Sub

Should you turn appTGGL back on at the end? – AJD Mar 22 '18 at 19:21 — AJD, Mar 22 '18 at 19:21
@ajd absolutely should. Thanks. – QHarr Mar 22 '18 at 19:22 — QHarr, Mar 22 '18 at 19:22

Export each spreadsheet to a separate workbook (Something except VBA)

1 Answers1