Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-02
7
Medium Priority
?
6,501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

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

Accepted Solution

by:
Rgonzo1971 earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

688 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