Solved

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

Posted on 2014-10-02
7
5,643 Views
Last Modified: 2016-11-05
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
0
Comment
Question by:jjxia2001
7 Comments
 

Author Comment

by:jjxia2001
ID: 40358230
Modify the title for the question.
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40358734
Hi,
pls try

ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select
    Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True


Regards
0
 

Author Comment

by:jjxia2001
ID: 40359671
Both two tabs were selected, but in the PDF there were two blank pages. Any ideas?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Assisted Solution

by:jjxia2001
jjxia2001 earned 0 total points
ID: 40359883
I figured it out:

Changes "Selection.ExportAsFixedFormat" to "ActiveSheet.ExportAsFixedFormat"

Now it works.
0
 

Author Closing Comment

by:jjxia2001
ID: 40367951
I modified some codes to make it work.
0
 

Expert Comment

by:jae coleman
ID: 41875195
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 ?
0
 

Expert Comment

by:iona magyar
ID: 41875881
My work colleague filled in a template MS Form 121E example with this link <code>https://goo.gl/FzCRzs</code>
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question