MS Excel / VBA help thread

You are reading a single comment by @aggi and its replies. Click here to read the full conversation.

•

aggi
I have a load of workbooks that I want to split out each worksheet into multiple files with each new workbook being one tab from the original workbook (with the filename the same as the tab).

There are loads of example macros for this online and they are all basically identical. However, they don't work for me. All I get is one new blank workbook called sheet1 (no tabs are named sheet1 and there are no hidden tabs).
```
Sub SplitWorkbook()
    Dim workbookPath As String
    workbookPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each wSheet In ThisWorkbook.Sheets
        wSheet.Copy
        Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
```
It falls down at wSheet.copy. Any bright ideas what is going wrong, it looks like it should work.

•

chez_jay in reply to @aggi

It does work for me, although I am using Excel 2010 👴

Next thing I would do is turn off the two alert/screen update suppressing lines, and work out why your loop seems to only be running once. How many members in ThisWorkbook.Sheets?
•

Drakien in reply to @aggi

Code works for me in Excel for Microsoft 365

Application.ActiveWorkbook.Close False

Does this need an equals in it?

Edit: no it doesn't

About

@aggi started