Export sheet collection to pdf

DrSlayer

New Member
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
 

Fluff

MrExcel MVP, Moderator
Office Version
365
Operating System
Windows
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
 

DrSlayer

New Member
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?
 

Fluff

MrExcel MVP, Moderator
Office Version
365
Operating System
Windows
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:= _
     "[COLOR=#ff0000]C:\MrExcel\[/COLOR]" & ActiveWorkbook.Name, Quality:=xlQualityStandard, IncludeDocProperties:= _
     True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
You'll need to change the part in red to suit
 

DrSlayer

New Member
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.
 

Fluff

MrExcel MVP, Moderator
Office Version
365
Operating System
Windows
Glad to help & thanks for the feedback
 

Some videos you may like

This Week's Hot Topics

Top