Solved

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

Posted on 2014-10-02
7
5,796 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 50

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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