Macro to SaveAs PDF but alter the name

[AmandaM]

Hi all! I am comfortable with excel but diving into Visual Basic has been slow. I'm sorry if this question has been repeated a million times. My searches haven't found a workable answer.


We have our client files in excel .. example "M, Amanda Qrtly Rpt.xlsx" in 4 different folders. The first 4 tabs of each file are saved as a PDF and sent to clients each quarter.

I need to define a variable that gets the current file name/path so I can use it to save as a pdf and saves it in a holding file for review, and adds the quarter ending date. the result should be "M, Amanda Qrtly Rpt 9.30.19.pdf" and saved in the holding file. I can update the date in the macro each quarter.

I think I'm close... and while I know what I need, I have no idea how to write the "something" part to be the current file name. (Yes, I'm looking for classes or books but would love to finish the quarter using this shortcut.)

sub savePDF()
'
' savePDF Macro
'
    
'
    Sheets(Array("Summary", "Analysis", "Chart", "Invoice")).Select
    Sheets("Summary").Activate
    ChDir "G:\Client Documents\_PDF Holding"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "G:\Client Documents\_PDF Holding\" & something & " 9.30.19.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub

Any and all suggestions are greatly appreciated!!!

~ AmandaM

 

[James006]

You are Welcome ...!!!

Thanks a lot for your Very Kind Thanks ...