Command Button with Macro to Print to PDF & Save to Folder on Desktop + PDF to be saved with Name in worksheet

Michelledryr

New Member
Joined
Sep 27, 2017
Messages
16
Hi Guys,

Hope you are able to assist me please, I am very new to the VBA codes

My workbook has 2 worksheets. Sheet 1 (Form) Sheet 2 (Result).

Result sheet pulls all the info from the Form sheet.

I would like a command button on the Form sheet which will print the Result sheet to PDF and save it on the desktop with the name from cells out of the result sheet.

Thus I require the command button to do the following please:

1. Print Result sheet to PDF
2. Save PDF with name from Result sheet B14 +G21+ B12
3. Save PDF in Folder on Desktop Named Quotes

Thanking you in advance for your kind assistance :)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Does this do what you want?

Code:
Public Sub CreateQuotePdf()

Dim outputFile As String

With Sheets("Result")
    outputFile = Environ("UserProfile") & "\Desktop\Quotes\"
    outputFile = outputFile & .Range("B14").Value & .Range("G21").Value & .Range("B12").Value & ".pdf"

    .UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputFile
End With

End Sub

WBD
 
Upvote 0
@WBD

Thank you so much, it works in general.

3 things that might need to change do you mind assisting me to correct the below please:

1. The Print area needs to be from A1 - H92
2. Its not pulling through the reference from G21 - and can one put spaces between the 3 references?
3. How do I get it to run automatically?

Thanks so much :)
 
Upvote 0
Updated code:

Code:
Public Sub CreateQuotePdf()

Dim outputFile As String

With Sheets("Result")
    outputFile = Environ("UserProfile") & "\Desktop\Quotes\"
    outputFile = outputFile & .Range("B14").Value & " " & .Range("G21").Value & " " & .Range("B12").Value & ".pdf"
    .Range("A1:H92").ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputFile
End With

End Sub

I've no idea about not fetching the G21 value - the code just fetches whatever value is in that cell.

To run automatically, go to the Developer ribbon in Excel and Insert a new Button Form Control. When you've placed the button, you can assign the above macro to it from the dialog box that gets created.

WBD
 
Upvote 0
Hi

I have tried implementing this code with my own amendments but i'm not winning:
this is what i'm using

Private Sub CommandButton1_Click()


Dim outputFile As String


With Sheets("Summary")
outputFile = Environ("UserProfile") & "\\dcpcifs01\Public_Data\HealthyCompany\MISC\Reports"
.Range("B2:U81").ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputFile
End With
End Sub

Suggestions?

Updated code:

Code:
Public Sub CreateQuotePdf()

Dim outputFile As String

With Sheets("Result")
    outputFile = Environ("UserProfile") & "\Desktop\Quotes\"
    outputFile = outputFile & .Range("B14").Value & " " & .Range("G21").Value & " " & .Range("B12").Value & ".pdf"
    .Range("A1:H92").ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputFile
End With

End Sub

I've no idea about not fetching the G21 value - the code just fetches whatever value is in that cell.

To run automatically, go to the Developer ribbon in Excel and Insert a new Button Form Control. When you've placed the button, you can assign the above macro to it from the dialog box that gets created.

WBD
 
Upvote 0
You forgot to add ".pdf" to the end of your outputFile String, here you go:


Private Sub CommandButton1_Click()


Dim outputFile As String


With Sheets("Summary")
outputFile = Environ("UserProfile") & "\\dcpcifs01\Public_Data\HealthyCompany\MISC\Reports" & ".pdf"
.Range("B2:U81").ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputFile
End With
End Sub
 
Upvote 0
hi

What if i have a different destination, say in D drive
Does it mean i have to add as follows D:\ .... ?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top