Export Multiple Sheets to a Single PDF File – Excel VBA

This tutorial will show you how to export multiple sheets of a workbook into a single PDF using Excel VBA. The code can be adapted. SkiptomaincontentSkiptoprimarysidebarSkiptofooterHome/VBA/ExportMultipleSheetstoaSinglePDFFile–ExcelVBAThistutorialwilldemonstratehowtoexportmultiplesheetsofaworkbookintoasinglePDFfileusingExcelVBA.Thecodecanbeseenbelowandadaptedforyourownuse.Ihavepreviouslywrittenatutorialonexportingallthesheetsofaworkbook toPDFusingVBA. Thismacrowillallowyoutobemorespecificastowhatsheetsareexported.ItwillalsoexporttoasinglePDFfile,ratherthanaseparatefileforeachsheet.SpecifyingtheSheetstobeExportedThekeytothemacroistoselectthesheetsthatyouwanttoexportfirst.Inthecodebelowthisisdonewiththefollowingline.Sheets(Array("Bradford","Kettering")).SelectThis referencesthesheetsusingtheirname.Youcouldalternativelyselectthesheetsusingtheirindexnumberlikebelow.Sheets(Array(2,4,5)).SelectAnotheralternativemaybethattheuserselectsthesheetsinExcel,andthenthemacroisrun.Inthiscommonscenarioofonlyexportingtheselectedsheets,youwouldnotneedthislineatallsoitcanberemovedfromthemacro.ExportingSpecificSheetstoaSinglePDFFile–VBACodeThisVBAcodecanbecopiedandpastedintoamodulefortheworkbook.Changethecodetoworkforyoursituation.SubExportAsPDF() DimFolderPathAsString FolderPath="C:\Users\Trainee1\Desktop\PDFs" MkDirFolderPath           Sheets(Array("Bradford","Kettering")).Select    ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=FolderPath&"\Sales",_        openafterpublish:=False,ignoreprintareas:=False     MsgBox"AllPDF'shavebeensuccessfullyexported." EndSubIfyouwerehopingtoexportsheetsfrommultipleworkbookssavedsomewhereinyournetwork,thencheckoutthistutorialonloopingthroughthefilesofafolderusingVBA.BycombiningthecodefromthetwomacrosyoucouldexportmanyPDF’swithoneclickofabutton.MoreawesomeExcelVBATutorialsLoopThroughtheSubfoldersofaDirectoryusingFileSystemObjectAutomaticallyCreateaTableofContentsinExcelUsingtheOffsetPropertyinVBARelatedPosts:WhatYouNeedtoKnowAboutDynamicArrayFormulasinExcelImportMultipleExcelFileswithMultipleSheetsinExcelExcelFormulatoFindtheCellAddressofaValueExcelFILTERFunction–TheBestFunctioninExcelReaderInteractionsCommentsThisisgreat–Thankyousomuchforshowingmethis.Ihavethisexactsituation–butIneedtoprintChartsinseparateexcelsheetsallintoonepdf.Iwouldliketohaveeachchartfitto1pagetalland1pagewideregardlessofwherethechartisineachsheetandregardlessofit’ssize.Doyouknowhowtodothis?I’mnewtoallprogrammingsoanythoughtsappreciated.ThanksagainReplyThanksforthisAlan.Thisisterrific.Ihavetocreatesheetsinaworkbookformyboss.WiththiscodeIcanprintaPDFdocumentwithallthesheetsonit.ManythanksPeterReplyYourwelcomePeter,thanks.ReplyHello.Iamtryingouthismacroanditallworkshowevericouldlikethefilenametobelinkedtacallintheexcelfilee.g.itshouldlinktocellC6insteadof“Sales”likeyourexampleabove.Isthereanywayofdoingthis?ReplyHiGeorgia,Sure,youcanadaptthatbitofcodeasfollows.Filename:=FolderPath&“\”&Worksheets(“Kettering”).Range(“C6”)AlanReplySuperCanyouexportonlyonepage(exampleallselectedsheetssecondpages)toonlyoneoutputPDF?ReplyI’msurethiswouldbepossibleusingtheprintsettings.ItisnotcodethatIhavetohandthough.ReplyI’mgettingaRuntimeerror‘9’SubscriptoutofrangefortheThisWorkbook.Sheets(Array(“Sum_instructed”,“Sum_pending”)).SelectReplyJustcheckthatthesheetnamesarecorrect,becausethattypeoferrorindicatesthatitcannotfindthosesheets.Also,thatthemacrointhesameworkbookfilethatyouarerunningiton.ThisWorkbookreferstotheworkbookthatthemacroresidesin.ReplyHello,howtochangetheorderinapdffile,howworksheetsareissued?Ineedthe“introduction”toalwaysbebefore“Sheet1”,butIalwaysgetthefirstpageas“Sheet1”inthecontentofthepdffile.Itriedbothwithoutluck:Spreadsheets(array(“introduction”,“page1”)).SelectSpreadsheets(array(“Sheet1”,“intro”)).SelectHowtochangeprintoutsequence?ReplyHelloThankyouforaverygreatvideo.Iwasjustwondering,isitpossibletogetthepossibilitytotypethenameofthefile,insteadofitiswritteninthecode….??“Filename:=FolderPath&“\Sales”BestregardsAllanReplySure,thenamecouldbepickedupfromarangeonthesheetormaybehaveaninputboxpromptyouifyouprefer.ReplyHiAllan,Couldyoupleasemodifythecodetohaveitpopupforthelocationtosavethefile?Thanks.ReplyThiscanbedoneusingapplication.FileDialog(msoFileDialogFolderPicker).IshowanexampleofusingthisinmyVBAcourse–http://bit.ly/37XSKfZReplyLeaveaReplyCancelreplyYouremailaddresswillnotbepublished.Requiredfieldsaremarked*Comment*Name*Email*WebsiteSavemyname,email,andwebsiteinthisbrowserforthenexttimeIcomment. ΔPrimarySidebarPopularPostsExcelFixturesandLeagueTableGeneratorExcelIFFunctionContainsText–APartialMatchinaCell5ReasonsWhyyourExcelFormulaisNotCalculatingExcelFormulatoDisplaytheSheetNameinaCell4AlternativestoNestedIFFormulasHowtoCreateaReverseFINDFormulaCheckifaValueisanEvenNumberHowtoHyperlinktoaHiddenWorksheetinExcelIFFunctioninPowerQueryIncludingNestedIFSConditionalFormattingMultipleColumns–3ExamplesreportthisadRecentPostsSEQUENCEFunctioninExcelSortbyDropDownListinExcelAdvancedSUMFunctionExamples–ThePowerofSUMDisplayNegativeTimeinExcelLookupMultipleValuesinExcelreportthisadreportthisadFooterContact[email protected]UsefulLinksCourseTopicsChartsCoursesDashboardsDownloadsExcelTipsFormulasMailMergeOfficePivotTablesPowerBIPowerPivotPowerQueryVBAJoinOurMailingListxx