I'm having a problem getting my code to compile - I'm not sure why.
The code itself is based off of this:
https://www.extendoffice.com/documents/excel/628-excel-split-workbook.html
It saves each individual worksheet as a excel file, with the worksheet name as the filename - this works fine.
I wanted to change the filename it saves as. Right now it only saves the name as the worksheetname.xls, however I want the name to be workbookname_worksheetname.xls. This is where I'm having problems. I think I tried something involving activeworkbook.name, but that gave me the whole file name, so when saving I got a crazy name like workbookname.xlsx_worksheetname.xls. I started looking into how I could get only the filename and it led me to these posts which I've tried to incorporate as well:
How do I use FileSystemObject in VBA? File name without extension name VBA
However, I'm getting a compile error: syntax error on the following line:
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xFilename & "_" & xWs.Name,
FileFormat:=39
Can someone help review and cleanup the code if necessary? Is there a simpler way to do this?
Sub Splitbook()
'https://www.extendoffice.com/documents/excel/1174-excel-split-data-into-multiple-worksheets-based-on-column.html
'https://www.extendoffice.com/documents/excel/628-excel-split-workbook.html
Dim xPath As String
Dim xFilename As String
'https://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba/3236348#3236348
'https://stackoverflow.com/questions/27923926/file-name-without-extension-name-vba
Dim fso As New Scripting.FileSystemObject
xPath = Application.ActiveWorkbook.Path
xFilename = fso.GetBaseName(ActiveWorkbook.Name)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xFilename & "_" & xWs.Name,
FileFormat:=39
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thank you.
