VBA to save all worksheets as separate .xlsx files - macOS

[ollyhughes1982]

Hi,

I found the following code online (How to split a workbook to separate Excel files in Excel?). I hoped this would achieve my goal of saving all worksheets within a single .xlsx file and save (export / split) them to separate .xlsx files in the same file path location:

Sub Splitbook()
'Updateby20140612
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

Is there something that needs changing for macOS? I have done a debug and it seems to be something with the 9th (file path) line (part in red text).

The other VBA code that I've used from the same website previously has always worked on my Mac no problem.

Thanks in advance,

Olly.

 

[Fluff]

Try using

Application.ActiveWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".xlsx"

 

[ollyhughes1982]

Try using

Application.ActiveWorkbook.SaveAs FileName:=xPath & Application.PathSeparator & xWs.Name & ".xlsx"

I tried that, but it unfortunately didn't work. It just gives me one new blank workbook called 'Sheet1'. No errors though.

New code as below:

Sub Splitbook()
'Updateby20140612
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 & Application.PathSeparator & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[ollyhughes1982]

This is an example of the type of file I'm trying to split out: Maps Example.xlsx

 

[Fluff]

Do you get any errors?

 

[ollyhughes1982]

Do you get any errors?

No, no errors. It looks like it has worked and the new file appears in the same location as the source file

 

[Fluff]

Ok, change this line

For Each xWs In ThisWorkbook.Sheets

from ThisWorkbook to ActiveWorkbook

 

[ollyhughes1982]

Ok, change this line

For Each xWs In ThisWorkbook.Sheets

from ThisWorkbook to ActiveWorkbook

Yes, that worked perfectly now. Thanks very much. My Guardian Angel as always!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ollyhughes1982]

You're welcome & thanks for the feedback.

Apologies, one more thing. Would it be easy to amend the code to make it so that it saves all files as csv, specifically CSV UTF-8?