Fixing Excel macro to save selected sheets as PDF

I have a macro to save a number of named sheets as a PDF which seems to have stopped working properly.  The resulting PDF is empty except for a tiny bit of text in a corner of the first page.  However, if I manually select the sheets I want, then save them as a PDF, they save fine and I can see all the contents I want.  Not sure if an upgrade to Excel as part of Office 365 is to blame or if I've saved the wrong macro.  I am wondering if I should delete the line:

IncludeDocProperties:=True, IgnorePrintAreas:=False, _

Can anyone see anything wrong with this?
 

Sub SavePDF()
        NewFile = Application.GetSaveAsFilename(InitialFileName:="the default name.pdf", _
           fileFilter:="PDF File (*.pdf), *.pdf", Title:="Save in your JOBNUM\ContractDocs folder")
           
        If NewFile <> False Then
            '
            'check newname is suitable....
            newname = NewFile
           
            Sheets(Array("Title Page", "Results", "Comments", "Insights", "About Us")).Select
            Selection.ExportAsFixedFormat Type:=xlTypePDF, _
               Filename:=newname, _
               Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
               
            Sheets("Title Page").Select
        End If
End Sub
colin_thamesAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, what about this?

Sub SavePDF()
    Dim wb As Workbook
    
    Application.DisplayAlerts = False
    NewFile = Application.GetSaveAsFilename(InitialFileName:="the default name.pdf", _
    fileFilter:="PDF File (*.pdf), *.pdf", Title:="Save in your JOBNUM\ContractDocs folder")
    
    If NewFile <> False Then
        '
        'check newname is suitable....
        NewName = NewFile
        Sheets(Array("Title Page", "Results", "Comments", "Insights", "About Us")).Copy
        Set wb = ActiveWorkbook
        
        wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        
        wb.Close False
        Sheets("Title Page").Select
    End If
End Sub

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Colin,

Please give this a try...

Sub SavePDF()
    Dim ws
    Application.DisplayAlerts = False
    NewFile = Application.GetSaveAsFilename(InitialFileName:="the default name.pdf", _
    fileFilter:="PDF File (*.pdf), *.pdf", Title:="Save in your JOBNUM\ContractDocs folder")
    
    If NewFile <> False Then
        '
        'check newname is suitable....
        NewName = NewFile
        For Each ws In Array("Title Page", "Results", "Comments", "Insights", "About Us")
        Sheets(ws).Select
        Sheets(ws).UsedRange.Cells.Select
        Next ws
        Sheets(Array("Title Page", "Results", "Comments", "Insights", "About Us")).Select
        
        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=NewName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
        Sheets("Title Page").Select
    End If
End Sub

Open in new window

0
 
colin_thamesAuthor Commented:
Many thanks for the response.  I am still having similar problems with the finished PDF using your suggested script.  Parts of pages cut off as if the print area has been set wrongly.  However, if I print as a PDF (using Microsoft Print to PDF) or save manually as a PDF the pages appear fine so I don;t think it's a print area issue.  I also tried setting  Ignore Print area as 'False' but this had no effect.

Not sure if this is relevant, but after running the macro the cells on each sheet are highlighted in green in exactly the areas that are showing in the PDF.

Really can't figure this.  Any ideas?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
colin_thamesAuthor Commented:
Fixed.  I tried recording a macro of saving manually as a PDf and noticed another line was added (in bold).  This seemed to work, though if you know why this line made a difference I'd be interested!

Sub SavePDF()
        NewFile = Application.GetSaveAsFilename(InitialFileName:="the default name.pdf", _
           fileFilter:="PDF File (*.pdf), *.pdf", Title:="Save in your JOBNUM\ContractDocs folder")
           
        If NewFile <> False Then
            '
            'check newname is suitable....
            newname = NewFile
           
            Sheets(Array("Title Page", "Results", "Comments", "Insights", "About Us")).Select
            Sheets("Title Page").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
               Filename:=newname, _
               Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
               
            Sheets("Title Page").Select
        End If
End Sub
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad that method worked and produced the desired output.
Did you try the code I suggested in my last post?
0
 
colin_thamesAuthor Commented:
No, sorry, my fix and yours overlapped, but kudos (and points) for your help.
0
 
colin_thamesAuthor Commented:
Selecting the first page in the series seemed to sort out the page layout and everything appeared in the saved PDF.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Colin! Glad your issue is resolved.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.