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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.