how to generate a worksheet into pdf when saving the excel?

[akika]

hi
pls help

In excel, i have 7 sheets.
When i save the excel file save or ctrl s,

how can i generate sheet 2 named 'calculation' in pdf format with a datetimestamp in a specific folder ?

file name: worksheetName<datetimestmp>.pdf
folder D:\test\

 

[mumps]

Try:

Sub SavePDF()
    ChDir "D:\test\"
    Sheets("Sheet2").ExportAsFixedFormat Type:=xlTypePDF, Filename:="calculation" & Replace(Format(Now(), "yyyy-mm-dd,h:mm AM/PM"), ":", ".") _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[akika]

im new to vba things.
1 more question pls
how can i put this code when the save button is click (file > save or ctrl S).
then it directly save the pdf wzout hving to click on run macro.

 

[mumps]

You can assign a short cut key to run the macro. Do the following:
-In the "View" tab in the top menu, click "Macros" at the far right.
-Click on "SavePDF" and click the "Options" button.
-Enter any letter such as the letter "s" in the box.
-Click "OK" and close the pop-up.
Now whenever you want to run the macro, hold down the CTRL key and press the letter "s" and the macro will run.