Macro works on Windows but doesnt work on Mac

[moustafaabdelhady]

I have created a macro to copy from one cell to another in another workbook.
Although works perfectly fine on windows, it doesnt work on Mac
I am not a pro, can anybody help to get it work on Mac please

Here is the Macro :

Sub Make_New_File()
'
'   Make_New_File() Macro
Application.ScreenUpdating = False


Dim Path As String
Dim filename As String
Dim filetoopen As String
Dim filetoopen2 As String
Dim Wbk As Workbook
Dim Ws As Worksheet




    Set sFolder = ThisWorkbook.Sheets("Database").Range("MainFolder")
    Set FValidation = ThisWorkbook.Sheets("Database").Range("FolderValidation")


    filetoopen = ThisWorkbook.Sheets("Database").Range("NewCustomerFile")
    filetoopen2 = ThisWorkbook.Sheets("Database").Range("NewCustomerFile2")


    If Dir(sFolder) <> (FValidation) Then
        Workbooks.Open (filetoopen2)


        'open if the folder from the main desktop
        Workbooks("Natuurelle.xlsb").Sheets("Klant | Patient Registratie").Activate
        Range("C2").Select
        Range("C" & Rows.Count).End(xlUp).Offset(0, 8).Select
        Selection.Copy




        Windows("Patient map sjabloon.xlsb").Activate
        Sheets("Patient map").Select
        Range("D7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


        Application.DisplayAlerts = False
        ActiveWorkbook.CheckCompatibility = False
        Path2 = ThisWorkbook.Sheets("Database").Range("CustomersFilesArchive")
        filename = Range("C1").Value & " - " & Range("D1")
        ActiveWorkbook.SaveAs filename:=Path2 & filename & ".xlsb", FileFormat:=xlExcel12, CreateBackup:=False
    
Else
       Workbooks.Open (filetoopen)
    
       'open if the folder from the other desktop
       Workbooks("Natuurelle.xlsb").Sheets("Klant | Patient Registratie").Activate
       Range("C2").Select
       Range("C" & Rows.Count).End(xlUp).Offset(0, 8).Select
       Selection.Copy


       Windows("Patient map sjabloon.xlsb").Activate
       Sheets("Patient map").Select
       Range("D7").Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
       Application.CutCopyMode = False
  
       Application.DisplayAlerts = False
       ActiveWorkbook.CheckCompatibility = False
       Path2 = ThisWorkbook.Sheets("Database").Range("CustomersFilesArchive2")
       filename = Range("C1").Value & " - " & Range("D1")
       ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsb", FileFormat:=xlExcel12, CreateBackup:=False


End If


MsgBox "Klant map is gemakt voor:" _
& "  " & Sheets("Patient map").Range("D1").Value


Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

Please explain what "doesn't work" means.

• Does the code run?
• Does it run but do nothing?
• Does it produce error messages? If so, what do the messages say?
• Does it produce unexpected/wrong results? If so, how do the results differ from what you expect?
• Does it hang?

What is the value of ThisWorkbook.Sheets("Database").Range("MainFolder") in both the Windows and Mac versions?

 

[moustafaabdelhady]

thank you so much for the quick reply

Answering your questions:

• Does the code run? yes
• Does it run but do nothing? it bugs at a certain point
• Does it produce error messages? If so, what do the messages say? yes it does, Runtime error9 subscript out of range.
• Does it produce unexpected/wrong results? If so, how do the results differ from what you expect? no results
• Does it hang?

It stops at the Line 21

where it says: Windows("Patient map sjabloon.xlsb").Activate

Error screen shot attached

 

[Norie]

Which workbook does Windows("Patient map sjabloon.xlsb") refer to?

Is it the workbook the code is in, the one you open in the code or another open workbook?

 

[moustafaabdelhady]

Which workbook does Windows("Patient map sjabloon.xlsb") refer to?

Is it the workbook the code is in, the one you open in the code or another open workbook?

It refers to the second workbook
The macro is in a workbook named ("Natuurelle")

 

[Norie]

By 'second' workbook do you mean the workbook open here?

        Workbooks.Open (filetoopen)

Or the one being opened here?

        Workbooks.Open (filetoopen)

Whichever it is you should create a reference to it when you open it and then use that in subsequent code.

 

[moustafaabdelhady]

Thanks again for your reply,
yes it is for the second file, I have declared it and the reference is in the cell in the main Workbook

Dim filetoopen As String
filetoopen = ThisWorkbook.Sheets("Database").Range("NewCustomerFile")
what I saw is that the macro opens the other workbook ("Patient map sjabloon.xlsb") but it accept the orders after that...
I dont know what should I change to make the code readable by Excel on Mac

 

[Norie]

This is getting the name/path to the file.

filetoopen = ThisWorkbook.Sheets("Database").Range("NewCustomerFile")

What I mean is to create an object reference to the workbook when you open like this.

Set wb = Workbooks.Open(filetoopen)

If you had that code then you could use the reference wb whenever you need to refer to the workbook later in the code.

That would avoid having to refer to the workbook by name which is the most likely reason for the problem you are having.

 

[moustafaabdelhady]

thanks for the reply, I will check and get back to you with the findings!