Excel to PDF Saving

austinandreikurt

New Member
Joined
Aug 25, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I would like to ask for help in saving an excel sheets into PDF form. Note that there are currently 3 excel sheets to be converted to PDF and it is just one file but the number of excel sheets should be the number of PDF page to be save. It will start with Sheet2 and so on. It is only 3 Sheets now but I need it to be extended up to the last excel sheet because the user may add another one. The excel sheets are named in order that it is in VBA form so the actual arrangement of the sheets is not a problem as the code will based it on the Sheet number (e.g Sheet2 to Sheet4). The only case here is to know what is the last Sheet number. Second, I want the PDF filename that is based on Cell G5 of Sheet2 and the folder where it will be saved will be left for the user to browse then the saving will continue. Thanks in advance!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
Sub SavePdf()
  Dim sName As String, spath As String
  Dim i As Long
  
  If Sheets.Count = 1 Then Exit Sub
  sName = Replace(Sheets(2).Range("G5").Value, "/", "-")
  If sName = "" Then
    MsgBox "Cell G5 is empty"
    Exit Sub
  End If
  For i = 2 To Sheets.Count
    Sheets(i).Select Replace:=i = 2
  Next
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    spath = .SelectedItems(1)
  End With

  ActiveSheet.ExportAsFixedFormat xlTypePDF, spath & "\" & sName, xlQualityStandard, True, False, , , False
  Sheets(2).Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,044
Members
412,565
Latest member
roberttaekim
Top