Print to PDF - Fit to 1 page wide + automate saved file name


Board Regular
May 22, 2017
Hi Everyone,

I am very new to VBA and need some help please. I have 2 work sheets which I want both to be saved into the 1 PDF. I can get this working with the following piece of code however I face 2 issues:

1) I can't seem to get it to fit to 1 page wide (I get only left half of worksheet in the pdf)
2) I prompt user for filename to save, how do I streamline this better? It very clunky and unprofessional to do this via an Inputbox but with my limited knowledge it's the only way I know how.

Here is the snippet:

VBA Code:
'Grab both sheets and print to PDF
Dim str As String, myfolder As String, myfile As String
Sheets(Array("Print Sheet1", "Print Sheet2")).Select
str = "Do you want to save these sheets to a single pdf file?" & Chr(10)
For Each sht In ActiveWindow.SelectedSheets
str = str & sht.Name & Chr(10)
Next sht

answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

With Application.FileDialog(msoFileDialogFolderPicker)
myfolder = .SelectedItems(1) & "\"
End With

myfile = InputBox("Enter filename", "Save as..")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfolder & myfile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Would really appreciate some guidance here please - thank you

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Latest member