Macro to save as PDF to file location selected from cell in spreadsheet-Excel 2011 for Mac

I am using Excel for Mac 2011 and do not know much about VBA or Macros

I have a spreadsheet that references data from a data sheet (FBB Data) and places in a document for printing (FBB Print) sheet. I have a Macro that will cycle through the FBB data list, update the FBB Print sheet and print out a page for each line it reads. This works well when going to a printer. I have tried to modify the Macro below however so that it will save each document as a PDF in a specific file location (FBB Contracts folder). Each PDF needs a different name however and I am trying to select that from cell L4 eg the cell might be EGMOFF. So what I am trying to achieve is for the file to save as EGMOFF.PDF and save it in the FBB Contracts folder

I am getting a debug error with the macro and I dont know how to fix it.
I Know the file structure is different for a MAC and im not sure Ive got that completely right in the Macro.

The debug error starts down the bottom of the Macro on the line that reads ActiveSheet.ExportAsFixed......

Hope you can help solve why it wont work

Thank you


Sub Save_to_PDF()
'
' Save_to_PDF Macro
'
Dim varResponse As Variant

varResponse = MsgBox(" ", vbYesNo, "Are you certain you want to Save ALL FBB Contracts as PDF?")

If varResponse <> vbYes Then Exit Sub

RowCount = Worksheets("FBB Data").Cells(Rows.Count, 1).End(xlUp).Row - 1

Worksheets("FBB Print").Select
For i = 1 To RowCount
Range("N1").Value = i
pdfname = Range("L4").Value
MyFN = "Mac SSD:Users:gemnnsw:Documents:FBB Contracts:" & pdfname & ".pdf"
On Error Resume Next
Kill (MyFN)
On Error GoTo 0
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=MyFN, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next i
'
End Sub

Thanks for the reply

It Runs but stops at the point where it starts ActiveSheet.Export
I get an application defined Object defined error

This is the same spot where the previous script stopped as well

Any other ideas?

Here is a sample of the spreadsheet. The macros I am having trouble with is 'Save to PDF'. It contains the original Macro I used but as indicated even after changing to the macro you have provided it still gets stuck.

Hope you can help

Regards

It is working very well. Thank you for the help