VBA - Save PDF with custom filename to specific location

[JC83UK]

Hoping someone can help me. I have most of the coding worked out but i cannot get it to agree to save with the custom file name.

I have two different pages which display data slightly differently depending on the user need. The result should be that they pick which report they want to generate as a PDF and then the save option comes up with the filename generated and the correct folder selected. Currently the only part that does not happen is the filename. I have attached the code below:

Sub PDF_EOD()

Dim Result As Integer
Dim Opendialog As Variant
Result = MsgBox("Was the Function Bar banking included in todays figures?", vbQuestion + vbYesNo)
If Result = vbYes Then
'''''
Sheets("EOD Banking Sheet 1").Select

Opendialog = Application.GetSaveAsFilename(Sheet1.[A10].Value, "PDF (*.pdf), *.pdf")

If Not Opendialog <> False Then MsgBox "File not saved.", vbCritical: Exit Sub

Dim fName As String
fName = "EOD" & Format(Date, "yyyymmdd")

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Z:\EOD Reports" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Saved.", vbInformation

Sheets("Main").Select

Else

Sheets("EOD Banking Sheet 2").Select

Opendialog = Application.GetSaveAsFilename(Sheet1.[A10].Value, "PDF (*.pdf), *.pdf")

If Not Opendialog <> False Then MsgBox "File not saved.", vbCritical: Exit Sub

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Z:\EOD Reports" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Saved.", vbInformation

Sheets("Main").Select

End If

End Sub

 

[Alex Blakenburg]

I am not 100% sure I know what you are trying to do.
See if this is heading in the right direction or not:-
I am assuming that the 2nd file name was supposed to come from sheet2 A10

Sub PDF_EOD()

    Dim Result As Integer
    Dim Opendialog As Variant
    Dim fName As String
    
    Result = MsgBox("Was the Function Bar banking included in todays figures?", vbQuestion + vbYesNo)
    If Result = vbYes Then
    '''''
    Sheets("EOD Banking Sheet 1").Select
    
    'Opendialog = Application.GetSaveAsFilename("C:\Users\ablak\Documents\Alex\Software\Excel\Test\TestCreatePDF\" & Sheet1.[A10].Value, "PDF (*.pdf), *.pdf")
    Opendialog = Application.GetSaveAsFilename("Z:\EOD Reports\" & Sheet1.[A10].Value, "PDF (*.pdf), *.pdf")
   
    If Not Opendialog <> False Then MsgBox "File not saved.", vbCritical: Exit Sub
    
    fName = Replace(Opendialog, ".pdf", " " & Format(Date, "yyyymmdd") & ".pdf")
    
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    fName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    MsgBox "Saved.", vbInformation
    
    Sheets("Main").Select
    
    Else
    
    Sheets("EOD Banking Sheet 2").Select
    
    Opendialog = Application.GetSaveAsFilename("Z:\EOD Reports\" & Sheet2.[A10].Value, "PDF (*.pdf), *.pdf")
    
    If Not Opendialog <> False Then MsgBox "File not saved.", vbCritical: Exit Sub
    
    fName = Replace(Opendialog, ".pdf", " " & Format(Date, "yyyymmdd") & ".pdf")
    
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    fName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    MsgBox "Saved.", vbInformation
    
    Sheets("Main").Select
    
    End If

End Sub

 

[JC83UK]

Hi Alex. Thanks for the reply. You are indeed on the correct track with what i am trying to achieve. The end result i am looking for is i want the user to run the macro and after selecting either yes or no be forced to save the file in a specified location with a filename that is date specific (EOD20211121.pdf which to the user would mean that the data in that pdf is for 21st November 2021 (Sheet1.[A10] is the location of the text EOD)).

 

[Alex Blakenburg]

So what is the code I sent you not doing that you need it to do ?