Forum Discussion
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 to save the code in a macro enabled Excel .xlsm so I can run it on an Excel.xslx file. I think the problem has something to do with the "ActiveWorkbook" command... Thanks for your help in advance!
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.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 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
2 Replies
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- Thanks so much mtarler! It works! One little thing can throw off the whole project!
