Export sheet collection to pdf
Results 1 to 6 of 6

Thread: Export sheet collection to pdf
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Export sheet collection to pdf

    Greetings,
    I need some assistance with sheet collections.
    I have a large spreadsheet that our designers copy onto each job file and use.
    The first 103 sheets do not get printed or exported to pdf. We only send out the new generated pages.
    I have written code to select all sheets from sheet #104 to the end and added them to a collection.
    My problem is exporting to a pdf.
    Here is the code I have to collect the sheets.
    Any ideas or help on this topic would be greatly appreciated.
    If there is a better way to accomplish this task, I'm all ears.
    Thanks,

    Code:
        Sub Print_Sheets()
             Dim col As Collection
            Set col = New Collection
         For Each wssheet In Worksheets
             i = i + 1
           If i > 104 Then
              col.Add wssheet
           End If
        Next
        'Debug section
           Debug.Print ThisWorkbook.Path & "\"
         For Each ele In col
           Debug.Print "ele in col:  " & ele.Name
        Next ele
        For Each ele In Sheets
           Debug.Print "ele in Sheets:  " & ele.Name
         Next ele
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Export sheet collection to pdf

    Hi & welcome to MrExcel
    How about
    Code:
    Sub ExpPdf()
    
       Dim i As Long
       
       For i = 104 To Worksheets.Count
          Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "C:\MrExcel\" & Sheets(i).Name, Quality:=xlQualityStandard, IncludeDocProperties:= _
            True, IgnorePrintAreas:=False, OpenAfterPublish:=False
       Next i
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheet collection to pdf

    Thank you for the quick reply.
    I have tried this code and get a Run-Time error '1004'. Document not saved or open.
    Is this code intending to export each sheet as an individual file?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Export sheet collection to pdf

    Is this code intending to export each sheet as an individual file?
    yes it is, if you want all sheets saved as 1 pdf then try
    Code:
    Sub ExpPdf()
    
       Dim i As Long
       Sheets(104).Select
       For i = 104 To Worksheets.Count
          Sheets(i).Select False
       Next i
       ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
         "C:\MrExcel\" & ActiveWorkbook.Name, Quality:=xlQualityStandard, IncludeDocProperties:= _
         True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End Sub
    You'll need to change the part in red to suit
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export sheet collection to pdf

    That did it. Thanks.
    Your original code worked after I check the spelling of my new folder.
    This has been of great help.
    Again. Thank you.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Export sheet collection to pdf

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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