I have a single Excel worksheet (PO Template). I am trying to code a macro to save the sheet (PO Template) as a PDF to a given filename (PDFName) and a given file location (POPath).
PDFName = test
POPath = S:\test\again\
The macro triggers a message box, but the (PDFName) reference is not part of the message.
It says: Are you sure you want to save this PO Requisition as .pdf?
It should say: Are you sure you want to save this PO Requisition as (PDFname).pdf?
Currently when I run the macro, no errors are triggered, but nothing happens. The Excel sheet isn't being saved as a PDF to the file location
Below is the macro:
' mac_PORequestPDF Macro
Dim ThisFile As String
ThisFile = Sheets("PO Template").Range("POPath").Value
If MsgBox("Are you sure you want to save this PO Requisition as " & (PDFName) & ".pdf?", vbYesNo) = vbYes Then
Sheets(Array("PO Template")).Copy ' Create a new workbook with this sheets
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFName, Quality:=xlQualityStandard, _