Forum Discussion

CreativeSteph's avatar
CreativeSteph
Copper Contributor
Jun 15, 2022
Solved

Excel VBA to put each sheet into an individual Excel file (split book)

Hello-   I'm trying to save a new Excel file for each sheet in an Excel file.  I found this code, but it only works if the code is written into the VBA module of the Excel I wish to parse.  I want ...
excel
  • mtarler's avatar
    mtarler
    Jun 15, 2022

    CreativeSteph  I haven't tested it, but assuming the code itself works it looks like you might only need to change the one part I highlighted in red:

    Sub Splitbook()

    Dim xPath As String
    xPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In ActiveWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

mtarler's avatar
mtarler
Silver Contributor
Jun 15, 2022

CreativeSteph  I haven't tested it, but assuming the code itself works it looks like you might only need to change the one part I highlighted in red:

Sub Splitbook()

Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ActiveWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

  • CreativeSteph's avatar
    CreativeSteph
    Copper Contributor
    Jun 15, 2022
    Thanks so much mtarler! It works! One little thing can throw off the whole project!

Resources