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

Application.ActiveWorkbook.SaveAs Filename:=xPath & Application.PathSeparator & xWs.Name, FileFormat:=62

 

[ollyhughes1982]

Try

Application.ActiveWorkbook.SaveAs Filename:=xPath & Application.PathSeparator & xWs.Name, FileFormat:=62

Thanks, but get an error with that line, unfortunately.

 

[Fluff]

Ok change the 62 to xlCSVUTF8

 

[ollyhughes1982]

still the same for me, sorry. same error again

 

[ollyhughes1982]

The current code:

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

 

[Fluff]

In that case I don't know, it works fine on a PC.

 

[ollyhughes1982]

In that case I don't know, it works fine on a PC.

ok, no problem. thanks.

 

[Fluff]

You're welcome.
Hopefully somebody with a Mac will be able to help.