Link to home
Start Free TrialLog in
Avatar of jjxia2001
jjxia2001

asked on

select multiple sheets to save as a pdf file in Excel VBA

I have the following code to save one sheet (the active one) as a pdf file.  But I need convert it to save multiple sheets ( 2 out of 6 in this case) as one pdf file.  I also know the sheet names I need to save.  I need help to modify the following codes from save one sheet to save two sheets.  Thanks in advance.  

Public Sub PDFActiveSheet()
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler
Dim User_Name As String
Dim FolderName As String
Set ws = ActiveSheet

'enter name and select folder for file
' start in current workbook folder
strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
            & "_" _
            & Replace(ActiveWorkbook.FullName, ".xlsm", "_") _
            & Format(Now(), "yyyymmdd\_hhmm") _
            & ".pdf"
User_Name = Environ("username")
   
FolderName = "C:\Users\" & User_Name & "\Desktop\"
strFile = FolderName & strFile

myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")
'Sheets("KE").Shapes("cmdSavetoPDF").Visible = False
If myFile <> "False" Then
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

   ' MsgBox "PDF file has been created."
End If

exitHandler:
'    Sheets("KE").Shapes("cmdSavetoPDF").Visible = True
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub
Avatar of jjxia2001
jjxia2001

ASKER

Modify the title for the question.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Both two tabs were selected, but in the PDF there were two blank pages. Any ideas?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I modified some codes to make it work.
Practical analysis - I am thankful for the info , Does someone know if my company would be able to get a blank MTA Reduced-Fare Metrocard App document to complete ?
My work colleague filled in a template MS Form 121E example with this link <code>https://goo.gl/FzCRzs</code>