Macro that previously worked now fails on latest version of Excel 365 for Mac

[MJ79]

I've used a macro for several years on an earlier version of Excel for Mac.
The Excel version was 2011, and the Macro looks like this:

Sub MOTM()
Dim ws As Worksheet, newWb As Workbook

Application.ScreenUpdating = False
For Each ws In Sheets(Array("6weekform", "MOTM"))
   ws.Copy
   Set newWb = ActiveWorkbook
   With newWb
      .SaveAs ws.Name & ".csv", xlCSVWindows
      .Close (False)
   End With
Next ws
Application.ScreenUpdating = True

End Sub

The Macro saved the workbook sheets "6weekform" and "MOTM" as two separate CSV files, keeping the original xlsm file separate.
Since recently moving to the new version of Excel 365 (16.53) for Mac (running Catalina 10.15.7), the Macro fails and produces this error:



When I click on Debug, it highlights this part of the Macro:



I can't find anywhere online that offers a solution for this, so any assistance any one can give me would be gratefully received.
I have several Macros like this one that used to produce dozens of CSV files from sheets, which I now have to individually manually save.

 

[MartinS]

Have you tried changing the file type to xlCSVMac?
Your code works in Windows as it should, unable to test on a Mac, but that would be my first suggestion.

 

[MJ79]

Thanks for trying, unfortunately same error, at the same point:

 

[MartinS]

I ran your code in Windows multiple times, and received the query about each file already existing (which you will need to manage the user selecting 'No' to this), but then tried using Excel for Mac and received the same error as you. I normally only develop VBA in Windows, so not familiar with the differences using a Mac I'm afraid.