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
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

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

Copper Contributor

Jun 15, 2022

Thanks so much mtarler! It works! One little thing can throw off the whole project!

Forum Discussion

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

Resources