Solved

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

Posted on 2014-10-02
7
5,509 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now