Save visible sheets to pdf, ignore hidden sheets

Question

I recorded a macro.

It works if all of the sheets are visible but when I hide a sheet it will not save to pdf.

This is the code.

Sub save_pdf() 
' 
' save_pdf Macro 
'

' 
Sheets(Array("TITLE", "CML", "CLUSTER", "ORS", "MOBILE", "YPS", "DEVICES", "PORTS")).Select 
Sheets("TITLE").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _ Sheets("MAIN").Range("customer_name") + " - Project Initiation_ Document.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True,_ IgnorePrintAreas:=False, OpenAfterPublish:=True 

Sheets("MAIN").Select

End Sub

Tim Williams · Answer 1 · 2016-09-23T22:34:47.143

1

Something like this:

Sub ExportVisible()
    Dim shts, sht As Worksheet, s, i As Long


    shts = Array("TITLE", "CML", "CLUSTER", "ORS", "MOBILE", "YPS", "DEVICES", "PORTS")
    i = 0

    For Each s In shts
        Set sht = ActiveWorkbook.Sheets(s)
        If sht.Visible = xlSheetVisible Then
            i = i + 1
            sht.Select (i = 1) '"replace" parameter true when i=1
        End If
    Next s

    'Sheets("TITLE").Activate '<<EDIT: remove this

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                  Filename:=Sheets("MAIN").Range("customer_name").Value & _
                  " - Project Initiation_Document.pdf ", _
                  Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                  IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

edited Sep 23 '16 at 22:34

answered Sep 20 '16 at 18:26

Tim Williams

154,628
8
97
125

thanks tim, but what it does is save only the title page. – RHON Sep 22 '16 at 10:21
Try commenting out the Sheets("TITLE").Activate line – Tim Williams Sep 22 '16 at 15:02
i am sorry tim, i am really a beginner in this field what do you mean by "commenting out the Sheets("TITLE").Activate"? – RHON Sep 23 '16 at 21:30
great sir tim, i got it on a search and after commenting out that line it works fine like what i am looking for. thank you very much. – RHON Sep 23 '16 at 21:53

score 0 · Answer 2 · answered Sep 20 '16 at 17:36

0

It's likely due to the use of .Select. We want to avoid using .Select wherever possible. Instead, just set a loop to go through each worksheet in your workbook.

Sub save_PDFs()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
    sht.ExportAsFixedFormat Type:=xlTypePDF, fileName:=Sheets("MAIN").Range("customer_name") + " - Project Initiation_ Document.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next sht
End Sub

However, if you want only visible sheets, then do this:

Sub save_PDFs()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible = True Then sht.ExportAsFixedFormat Type:=xlTypePDF, fileName:=Sheets("MAIN").Range("customer_name") + " - Project Initiation_ Document.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next sht
End Sub

answered Sep 20 '16 at 17:36

BruceWayne

22,923
15
65
110

1

OP wants to export the sheets into a single PDF - the `Select` is required for that. – Tim Williams Sep 20 '16 at 18:18
@TimWilliams - Oh, really? That's the first (that I can recall) where `.Select` is required. How come I can't just add `sht.Select` and replace `sht.ExportAs...` with `ActiveSheet.ExportAs...`? That also throws an error for me. – BruceWayne Sep 20 '16 at 18:28
@TimWilliams - `Workbook.ExportAsFixedFormat` skips hidden sheets (at least in 2013). See [this answer](https://stackoverflow.com/a/36107539/4088852). – Comintern Sep 20 '16 at 23:52
@bruce hi bruce what it does is to save the main page where the checkboxes and other form located. actually i am trying to avoid to print the main page. thanks! – RHON Sep 22 '16 at 10:27
"@TimWilliams - Workbook.ExportAsFixedFormat skips hidden sheets (at least in 2013). See this answer. – Comintern" this link solved my problem, thanks Comintern!! – RHON Sep 22 '16 at 10:52

Save visible sheets to pdf, ignore hidden sheets

2 Answers2