Excel to PDF Saving

[austinandreikurt]

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!

 

[DanteAmor]

Try this

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