+ Reply to Thread
Results 1 to 2 of 2

Export all worksheets to PDF as the sheet name

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Export all worksheets to PDF as the sheet name

    This code does one sheet at a time but that doesn't help me much. I need it to save multiple sheets separately and named as the sheet name. How can I do that?

    Sub PDFActiveSheet()
    'www.contextures.com
    'for Excel 2010 and later
    Dim wsA As Worksheet
    Dim wbA As Workbook
    Dim strName As String
    Dim strPath As String
    Dim strFile As String
    Dim strPathFile As String
    Dim myFile As Variant
    On Error GoTo errHandler
    
    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    
    
    'get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = "" Then
      strPath = Application.DefaultFilePath
    End If
    strPath = strPath & "\"
    
    'replace spaces and periods in sheet name
    strName = Replace(wsA.Name, " ", "")
    strName = Replace(strName, ".", "_")
    
    'create default name for savng file
    strFile = strName & "_" & ".pdf"
    strPathFile = strPath & strFile
    
    'use can enter name and
    ' select folder for file
    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strPathFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")
    
    'export to PDF if a folder was selected
    If myFile <> "False" Then
        wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        'confirmation message with file info
        MsgBox "PDF file has been created: " _
          & vbCrLf _
          & myFile
    End If
    
    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
    End Sub

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,015

    Re: Export all worksheets to PDF as the sheet name

    .
    This macro saves all sheets in the workbook as individual PDFs, in the DOCUMENTS folder :

    Option Explicit
    
    Sub createPDFfiles()
        Dim ws As Worksheet
        Dim Fname As String
        For Each ws In ActiveWorkbook.Worksheets
            On Error Resume Next 'Continue if an error occurs
    
            ' Name PDF files based on the worksheet Index
            Fname = "Sheet " & ws.Name ' You can delete Sheet and use only Fname = ws.Name. That would give you
                                        'just the sheet name. Leaving the line as is, the result is :
                                        'Sheet Sheet1; and Sheet Sheet2; and Sheet Sheet3
    
            ' If you want to name the PDF files differently just change the Fname variable above to
            ' whatever you like. For example if you changed Fname to:
            '
            '  Fname =  “C:\myFolder\pdfs\” & ActiveWorkbook.Name & "-" & ws.Name
            '
            '  The files would be stored in C:\myFolder\pdfs, and named using the
            ' spreadsheet file name and the worksheet name.
            '
            ' WARNING: Using worksheet names may cause errors if the  names contain characters that Windows
            ' does not accept in file names.
            '
            ws.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=Fname, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False
        Next ws
    End Sub
    You can specify a folder by : Fname = “C:\myFolder\pdfs\” & ws.Name

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Export Worksheets
    By vio.coman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-06-2014, 04:15 AM
  2. Export Specified Worksheets
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-19-2014, 01:45 PM
  3. [SOLVED] Export Specified Worksheets to PDF
    By dmarzean in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2014, 03:30 AM
  4. Export data from master sheet to seperate worksheets dependent on 1-2 conditions
    By Cupomaxwell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-10-2013, 10:04 AM
  5. Replies: 1
    Last Post: 10-30-2012, 06:34 PM
  6. Export worksheets
    By mjhopler in forum Excel General
    Replies: 2
    Last Post: 06-14-2010, 01:02 PM
  7. XML Export and worksheets
    By WHoit in forum Excel General
    Replies: 0
    Last Post: 03-15-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1