Save as PDF Button

[Surferol]

Hi all,

I would like to make a button on each sheet of a workbook which will save the sheet (excluding the button) as a PDF to the desktop.
Each sheets starts off as a blank sheet, the blank sheet is copied and data entered, sheet renamed as needed.

The data on each sheet will be reviewed and amended every few months at which point a new PDF needs to be produced to be saved (another program will store the PDF to be displayed over mapping)

I want to click a "Save As PDF" button which will save the sheet to PDF on the desktop, and name the file the same as the TAB name.
(basically taking away multiple clicks and sources of error of saving the wrong name to the wrong place.

Hope this is possible,
Thanks

 

[Domenic]

For each button, assign the following macro. Note that if your desktop already has a file with the same name, it will be overwritten.

Sub SaveAsPDF()

    Dim oWSHShell As Object
    Set oWSHShell = CreateObject("WScript.Shell")
    
    Dim saveAsFolder As String
    saveAsFolder = oWSHShell.SpecialFolders("Desktop")
    
    With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFolder & "\" & .Name & ".pdf"
    End With
    
    Set oWSHShell = Nothing
    
End Sub

Hope this helps!

 

[Surferol]

Hi Domenic,
Forgive me, this is my first attempt at any form of macro or VBA.
I had a Macro recorded and opened VBA to enter what you show above and doesn't seem to work.

 

[petertenthije]

Instead of
saveAsFolder = oWSHShell.SpecialFolders("Desktop")

Try
saveAsFolder ="C:\Foldername\Foldername\"

It's probably the difference between Windows and MacOS that causes this.

 

[Kenneth Hobson]

Your button is calling some other routine. You have to assign SaveAsPDF macro to the form button or put that word into your activex control. Which it is depends on your command button's control type.