Print Multiple Excel Sheets to Single PDF File with VBA (6 ...

Apply Macro to Export Multiple Excel Sheets to PDF inside a Created Folder Home»Macros&ExcelVBA»PrintMultipleExcelSheetstoSinglePDFFilewithVBA(6Criteria) Macros&ExcelVBA PrintMultipleExcelSheetstoSinglePDFFilewithVBA(6Criteria) SanjidaAhmed May8,2022 0 WhenwehavesomanyworksheetsinourExcelworkbook,sometimesitisconvenienttoprintallthosesheetsinasinglePDFfileforbetterreadability.ImplementingVBAisthemosteffective,quickest,andsafestmethodtorunanyoperationinExcel.Inthisarticle,wewillshowyou6differentcriteriaonhowtoprintmultipleExcelsheetstoasinglePDFwiththeVBAmacro. TableofContents hide DownloadWorkbook 6CriteriatoPrintMultipleExcelSheetstoSinglePDFwithVBA 1.EmbedVBAtoPrintAllSheetsofanExcelWorkbookinaSinglePDF 2.ImplementVBAtoPrintMultipleWorksheetsfromSelectioninExcel 3.ApplyVBAMacrotoPrintSpecificSheetsintoSinglePDFfromaWorkbook 4.EmbedVBAtoPrintMultipleSheetsasPDFandRenameAccordingtoCellValue 5.ApplyMacrotoExportMultipleExcelSheetstoPDFinsideaCreatedFolder 6.ImplementVBAtoExportActiveWorksheettoPDFMultipleTimesintheWorkingFolder Conclusion RelatedArticles DownloadWorkbook YoucandownloadthefreepracticeExcelworkbookfromhere. PrintMultipleSheetstoSinglePDF.xlsm 6CriteriatoPrintMultipleExcelSheetstoSinglePDFwithVBA Followingthissection,wewilldiscuss6differentcriteriaonhowtoprintmultipleExcelsheetstoasinglePDFwiththeVBA. Butfirst,letusintroduceourExcelworkbooktoyousothatyouwillbeabletounderstandtheoutcomethatwearetryingtoretrieveforthisarticle. WehaveanExcelworkbookconsistingofthreeworksheets.ThefirstworksheetnameisSheet1,thedatainthissheetareshowninthepicturebelow. ThesecondworksheetnameisSheet2,thedatainthissheetareshowninthefollowingpicture. AndthethirdworksheetnameisSheet3,thedatainthissheetareshowninthepicturebelow. WewilllearnhowtoprintalltheseExcelsheetsintoasinglePDFfilewithVBA. 1.EmbedVBAtoPrintAllSheetsofanExcelWorkbookinaSinglePDF Inthissection,youwilllearnhowtoprintallthesheets(Sheet1,Sheet2,Sheet3)fromtheExcelworkbook(introducedbefore)intoasinglePDFfilewithVBA. Let’slearnthestepsonhowtogetthat. Steps: Inthebeginning,pressAlt+F11onyourkeyboardorgotothetabDeveloper->VisualBasictoopenVisualBasicEditor. Next,inthepop-upcodewindow,fromthemenubar,clickInsert->Module. Then,copythefollowingcodeandpasteitintothecodewindow. SubPrintAllSheetToPdf() ForEachiSheetInActiveWorkbook.Worksheets    Worksheets(iSheet.Name).SelectFalse NextiSheet WithApplication.FileDialog(msoFileDialogFolderPicker)    .Show    iFolder=.SelectedItems(1)&"\" EndWith iFile=InputBox("EnterNewFileName","PDFFileName") ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFolder&iFile,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=True EndSub Yourcodeisnowreadytorun. Now,pressF5onyourkeyboardorfromthemenubarselectRun->RunSub/UserForm.YoucanalsojustclickonthesmallRuniconinthesub-menubartorunthemacro. Aftersuccessfulcodeexecution,youwillbeaskedtoselectthefolderwhereyouwanttosavethePDFfile.Inourcase,wewillsavethefileinthe“ExcelDemy”folderintheLocalDisk(C:). Afterselectingthenewfilepath,clickOK. Then,apop-upinputboxwillappear.Itwillaskyouforaname.WriteanynameforyournewPDFfile.Inourcase,wenamedournewPDFfile“StudentInformation”. Later,clickOK. Whileyouaredoingthese,youwillnoticethatallthesheetsinyourworkbookareautomaticallyselected(noticeintheimagebelow). AfterprovidingthenameforthenewPDFfile,youwillseethenewlycreatedPDFfilewillautomaticallyopen. WenamedourPDFfile“StudentInformation”.So,ifyoulookatthefollowinggifyouwillseethatthenameofthePDFfileis“StudentInformation”,andithasthreepageswhereeachpagecarriesthedatafromeachoftheworksheetsfromtheExcelworkbook.Page1ofthePDFfileholdsthedatafromSheet1fromtheworkbook,Page2holdsthedatafromSheet2,andPage3holdsthedatafromSheet3fromtheworkbook. Nowlet’scheckwhetherthe“ExcelDemy”folderthatweselectedasthestorageofourPDFfile,hasthefileornot. Asyoucanseefromthepicturebelow,wehavethenewlycreated“StudentInformation”PDFfileinthe“ExcelDemy”folderinDriveC. YouwanttoprintthePDFfile,right? Openthefile. ThengotoFile->Print.Alternatively,youcanpressCtrl+Pfromyourkeyboardtoprintthefile. ItwillopenthePrintIfyouwant,thenyoucanmodifythepagesetupaccordingtoyourneed.Then,pressPrint. YournewlycreatedPDFfilefrommultipleExcelsheetswillstartprinting.ThisishowyouprintallthesheetsfromtheExcelworkbookintoasinglePDFfilewithVBA. VBACodeExplanation SubPrintAllSheetToPdf() Namethesub-procedureofthemacro. ForEachiSheetInActiveWorkbook.Worksheets Worksheets(iSheet.Name).SelectFalse NextiSheet Selectallworksheetsintheactiveworkbook. WithApplication.FileDialog(msoFileDialogFolderPicker) .Show iFolder=.SelectedItems(1)&"\" EndWith AskforadirectorytosavethenewlycreatedPDFfilein. iFile=InputBox("EnterNewFileName","PDFFileName") AskforanameforthenewlycreatedPDFfile. ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFolder&iFile,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=True SaveallworksheetsfromtheactiveworkbookasasinglePDFfile– Inthepathaddressandwiththefilename WiththeStandard WiththeWordfileproperties. WithoutthePrintAreas. Willautomaticallyopenafterthecodeexecution. EndSub Endsthesub-procedureofthemacro. ReadMore:HowtoPrintAllSheetsinExcel(3Methods) 2.ImplementVBAtoPrintMultipleWorksheetsfromSelectioninExcel Inthissection,youwilllearnhowtoprintmultipleworksheetsbyselectingthemmanuallyinExcelwithVBA. Let’sseehowtoachievethatwithVBAmacro. Steps: Samewayasbefore,openVisualBasicEditorfromtheDevelopertabandInsertaModuleinthecodewindow. Then,copythefollowingcodeandpasteitintothecodewindow. SubPrintActiveSheetToPdf() DimmsgAsString DimiFolderAsString DimiFileAsString msg="Doyouwanttosavetheseworksheetstoasinglepdffile?"&amp&nbsp&Chr(10) ForEachiSheetInActiveWindow.SelectedSheets msg=msg&iSheet.Name&Chr(10) NextiSheet iText=MsgBox(msg,vbYesNo,"ConfirmtoSaveasPDF...") IfiText=vbNoThenExitSub WithApplication.FileDialog(msoFileDialogFolderPicker) .Show iFolder=.SelectedItems(1)&"\" EndWith iFile=InputBox("EnterNewFileName","PDFFileName") ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFolder&iFile,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=True EndSub Yourcodeisnowreadytorun. Butbeforerunningthiscode,first,gobacktotheworkbookandselectallthesheetsthatyouwanttoexport.YoucanselectmultiplesheetsbyclickingonthesheetswhileholdingdowntheCtrlkey. Then,Runthemacro. Therewillbeapop-upbox,showingyouallthesheetsthatyouselectedtoconvertintoasinglePDF. Afterconfirming,clickYes. Similartotheprevioussection,youwillbetakentotheBrowsewindowwhereyouwillbeaskedtoselectthefolderwhereyouwanttosavethePDFfile.Inourcase,wewillsavethefileinthe“ExcelDemy”folderintheLocalDisk(C:). Afterselectingthenewfilepath,clickOK. Then,apop-upinputboxwillappear.Itwillaskyouforaname.WriteanynameforyournewPDFfile.Inourcase,wenamedournewPDFfile“StudentInformation(Selection)”. Later,clickOK. AfterprovidingthenameforthenewPDFfile,youwillseethenewlycreatedPDFfilewillautomaticallyopen. WenamedourPDFfile“StudentInformation(Selection)”.So,ifyoulookatthefollowinggifyouwillseethatthenameofthePDFfileis“StudentInformation(Selection)”,andithasthreepageswhereeachpagecarriesthedatafromeachoftheworksheetsfromtheExcelworkbook.Page1ofthePDFfileholdsthedatafromSheet1fromtheworkbook,Page2holdsthedatafromSheet2,andPage3holdsthedatafromSheet3fromtheworkbook. Nowlet’scheckwhetherthe“ExcelDemy”folderthatweselectedasthestorageofourPDFfile,hasthefileornot. Asyoucanseefromthepicturebelow,wehavethenewlycreated“StudentInformation(Selection)”PDFfileinthe“ExcelDemy”folderinDriveC. Now,toprintthePDFfile,youhavetofollowthestepsbelow. First,openthefile. Then,gotoFile->Print.Alternatively,youcanpressCtrl+Pfromyourkeyboardtoprintthefile. ItwillopenthePrintIfyouwant,thenyoucanmodifythepagesetupaccordingtoyourneed.Then,pressPrint. YournewlycreatedPDFfilefrommultipleExcelsheetswillstartprinting.ThisishowyouprintallthesheetsbyselectionfromtheExcelworkbookintoasinglePDFfilewithVBA. VBACodeExplanation SubPrintActiveSheetToPdf() Namethesub-procedureofthemacro. DimmsgAsString DimiFolderAsString DimiFileAsString Declarethevariables. msg="Doyouwanttosavetheseworksheetstoasinglepdffile?"&amp&nbsp&Chr(10) ForEachiSheetInActiveWindow.SelectedSheets msg=msg&iSheet.Name&Chr(10) NextiSheet Selectonlytheselectedworksheetsintheactiveworkbook.ShowthesheetnamesconcatenatedwithaquestioninaMsgBoxtoconfirm.Chr(10)isacarriagereturn. iText=MsgBox(msg,vbYesNo,"ConfirmtoSaveasPDF...") IfiText=vbNoThenExitSub ExecutethetaskaccordingtoYesorNoconfirmation.IfYes,thencontinuewiththeprocedure.IfNo,thenexittheprocedure. WithApplication.FileDialog(msoFileDialogFolderPicker) .Show iFolder=.SelectedItems(1)&"\" EndWith AskforadirectorytosavethenewlycreatedPDFfilein. iFile=InputBox("EnterNewFileName","PDFFileName") AskforanameforthenewlycreatedPDFfile. ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFolder&iFile,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=True SaveallworksheetsfromtheactiveworkbookasasinglePDFfile– Inthepathaddressandwiththefilename WiththeStandard WiththeWordfileproperties. WithoutthePrintAreas. Willautomaticallyopenafterthecodeexecution. EndSub Endsthesub-procedureofthemacro. ReadMore:HowtoPrintMultipleSheetsinExcel(7DifferentMethods) 3.ApplyVBAMacrotoPrintSpecificSheetsintoSinglePDFfromaWorkbook Tillnow,wehavebeenlearninghowtoprintallthesheetsexistingintheExcelworkbookintoasinglePDFfilewithVBA.Butwhatifyoudon’twanttoprintallthesheets?Whatifyouwanttoprintsomespecificsheetsfromtheworkbook? YoucandothateasilywithafewlinesofVBA.Thestepstogetthataregivenbelow. Steps: Asshownbefore,openVisualBasicEditorfromtheDevelopertabandInsertaModuleinthecodewindow. Then,copythefollowingcodeandpasteitintothecodewindow. SubPrintSpecificSheetsToPdf()    DimiSheetsAsVariant    iSheets=Array("Sheet1","Sheet2")    ThisWorkbook.Sheets(iSheets).PrintOut EndSub SubPrintSpecificSheetsToPdfWithLoop()    DimiSheets()AsString    DimiCountAsLong    ReDimiSheets(1ToThisWorkbook.Sheets.Count)    ForiCount=LBound(iSheets)ToUBound(iSheets)        iSheets(iCount)=ThisWorkbook.Sheets(iCount).Name    NextiCount    ThisWorkbook.Sheets(iSheets).PrintOut EndSub Yourcodeisnowreadytorun. Now,lookcloselyatthecode.Doyouseetheindicatedpartinthefollowingimage?Inthatlineofcode,wehardcodedonlythespecifiedsheetname–Sheet1andSheet2–thatwewanttoexportintoPDF. Next,Runthemacro.YouwillbebrieflynotifiedbyaPrintingpop-upbox. YouwillbebroughttotheFileselectionIfyouwanttosavethenewlycreatedPDFfileinthefilepaththatthewindowisshowing,thenjustsimplypressOK. Or,ifyouwanttostorethePDFfileatadifferentaddress,thenclickBrowse. YouwillbetakentotheSaveAswindow,whereyougettoselectthefilepaththatyouwantthePDFtostore.Inourcase,wewillsavethefileinthe“ExcelDemy”folderintheLocalDisk(C:).So,weselectthat. Afterthat,youwillalsobeabletonamethePDFfile.Youcaneitherkeepthefilenamethatthesystemisshowingyou.ThenamethatthesystemwillshowyouwillbethenamesimilartotheExcelfilethatyouareworkingin.Forinstance,theExcelsheets(Sheet1,Sheet2,Sheet3)thatwearetryingtoexportintoPDF,areintheExcelworkbooknamed“PrintMultipleSheetstoSinglePDF”.That’swhythesystemisshowingthisnameforthenewPDFfileinourcase. Afterselectingthenewfilepathandprovidingthefilename,clickSave. OrifyouwanttosaveanewnameforthePDFfile,youcansimplyjustreplacetheoldnamewiththenewoneintheFilenameInourcase,wenamedournewPDFfile“StudentInformation(Specific)”. Then,clickSave. YouwillbeabletoseethefilepathyouhavejustselectedwiththenewfilenameintheFileselection Later,clickOK. AfterprovidingthepathandthenameforthenewPDFfile,youwillseethatthenewlycreatedPDFfilewillautomaticallyopen. WenamedourPDFfile“StudentInformation(Specific)”.So,ifyoulookatthefollowinggifyouwillseethatthenameofthePDFfileis“StudentInformation(Specific)”andithastwopages,aswehaveprovidedonlySheet1andSheet2inthecode.EachpagefromthePDFcarriesthedatafromeachoftheworksheetsspecifiedintheExcelworkbook.Page1ofthePDFfileholdsthedatafromSheet1fromtheworkbookandPage2holdsthedatafromSheet2fromtheworkbook. Nowlet’scheckwhetherthe“ExcelDemy”folderthatweselectedasthestorageofourPDFfile,hasthefileornot. Asyoucanseefromthepicturebelow,wehavethenewlycreated“StudentInformation(Specific)”PDFfileinthe“ExcelDemy”folderinDriveC. Now,toprintthePDFfile,youhavetofollowthestepsbelow. First,openthefile. Then,gotoFile->Print.Alternatively,youcanpressCtrl+Pfromyourkeyboardtoprintthefile. ItwillopenthePrintIfyouwant,thenyoucanmodifythepagesetupaccordingtoyourneed.Then,pressPrint. YournewlycreatedPDFfilefrommultiplespecifiedExcelsheetswillstartprinting.ThisishowyouprintthemultiplespecifiedsheetsfromtheExcelworkbookintoasinglePDFfilewithVBA. VBACodeExplanation SubPrintSpecificSheetsToPdf() Namethesub-procedureofthemacro. DimiSheetsAsVariant Declarethevariabletostorethemultiplesheets. iSheets=Array("Sheet1","Sheet2") Storethesheetsthatwillbeexportedinthedeclaredarrayvariable. ThisWorkbook.Sheets(iSheets).PrintOut PrintsthegroupofsheetswiththePrintOutfunction. EndSub Endsthesub-procedureofthemacro. SubPrintSpecificSheetsToPdfWithLoop() Namethesub-procedureofthemacrothatwillperformtheloopoperation. DimiSheets()AsString DimiCountAsLong Declarethevariables. ReDimiSheets(1ToThisWorkbook.Sheets.Count) Re-declarethearrayvariable.Thistimeitstoresthenumberofthetotalsheetcountinit. ForiCount=LBound(iSheets)ToUBound(iSheets) iSheets(iCount)=ThisWorkbook.Sheets(iCount).Name NextiCount Thispartofthecodestartsiteratingfromthesmallestsubscripttothelargestsubscriptofthearrayandstoresthearrayvalueinthevariable.Itcontinuesdoingthisuntilitfinishesscanningthroughthewholearray. ThisWorkbook.Sheets(iSheets).PrintOut PrintsthegroupofsheetswiththePrintOutfunction. EndSub Endsthesub-procedureofthemacro. ReadMore:HowtoPrintSpecificSheetsUsingVBAMacroinExcel(4Ways) SimilarReadings: PrintTitlesinExcelIsDisabled,HowtoEnableIt? HowtoPrintHorizontallyinExcel(4Methods) HowtoPrintGridlineswithEmptyCellsinExcel(2Methods) ExcelVBA:PrintUserFormtoFitonaPage(2Methods) HowtoSetPrintTitlesinExcel(2Methods) 4.EmbedVBAtoPrintMultipleSheetsasPDFandRenameAccordingtoCellValue Untilnow,youhavebeenmanuallyprovidingthenewlycreatedPDFfilename.ButthissectionwillteachyouhowyoucanprintmultiplesheetsasasinglePDFandnamethefileautomaticallyaccordingtothecellvaluethatyouprovideinthecode. ThestepstogetthatwiththeVBAmacroaregivenbelow. Steps: Atfirst,openVisualBasicEditorfromtheDevelopertabandInsertaModuleinthecodewindow. Then,copythefollowingcodeandpasteitintothecodewindow. OptionExplicit PublicSubPrintSpecificSheetsToPdfWithRename()    DimiSheetListAsVariant    DimiSheetAsWorksheet    DimiFileNameAsString    DimiFilePathAsString    SetiSheet=ThisWorkbook.Sheets("Sheet1")    iSheetList=Array("Sheet1","Sheet2")    iFilePath="C:\ExcelDemy\"    WithiSheet        iFileName=iFilePath&.Range("B5").Value&""&.Range("C5").Value&"-"&.Range("D5").Value&".pdf"    EndWith    ThisWorkbook.Sheets(iSheetList).Select    iSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFileName,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=True    iSheet.Select EndSub Yourcodeisnowreadytorun. Now,beforeexecuting,lookcloselyatthecode.Doyouseetheindicatedpartinthefollowingimage? Inthefirstlinefromtheindicatedpart,weprovidedSheet1.Itmeansthecellvaluesthatwewillbetakingtonameourfile,areinthatsheet. Inthesecondline,bypassingtheSheet1andSheet2insidethearrayasparameters,wearedefiningthesheetsthatwillbeexportedintoPDF. Inthethirdline,wewantedtostoreourPDFfileintheLocalDriveCinsidethe“ExcelDemy”folder.Youmustwritethepathaddresswhereyouwantyourfiletosave. Nowagain,considerthefollowingimagetounderstandthenamingsystemforthePDFfile. Inthispartofthecode,weprovidedthreecellnumbers,CellB5,C5andD5fromSheet1.CellB5hasthevalue“JohnCena”,CellC5hasthevalue“101”andCellD5hasthevalue“89”.So,thenameforthenewlycreatedPDFfilewillbe“JohnCena101-89”,wealsoprovidedahyphen(-)betweenCellC5andD5inthecode. Now,RunthemacroandyouwillseethenewlycreatedPDFfilewillautomaticallyopen. IfyoulookatthefollowinggifyouwillseethatthenameofthePDFfileis“JohnCena101-89”andithastwopages,aswehaveprovidedonlySheet1andSheet2inthecode.EachpagefromthePDFcarriesthedatafromeachoftheworksheetsspecifiedintheExcelworkbook.Page1ofthePDFfileholdsthedatafromSheet1fromtheworkbookandPage2holdsthedatafromSheet2fromtheworkbook. Nowlet’scheckwhetherthe“ExcelDemy”folderthatwehardcodedasthestorageofourPDFfile,hasthefileornot. Asyoucanseefromthepicturebelow,wehavethenewlycreated“JohnCena101-89”PDFfileinthe“ExcelDemy”folderinDriveC. Now,wewanttoprintthefile,right?ToprintthePDFfile,youhavetofollowthestepsbelow. First,openthefile. Then,gotoFile->Print.Alternatively,youcanpressCtrl+Pfromyourkeyboardtoprintthefile. ItwillopenthePrintIfyouwant,thenyoucanmodifythepagesetupaccordingtoyourneed.Then,pressPrint. YournewlycreatedPDFfilefrommultiplehardcodedExcelsheetswillstartprinting.ThisishowyouprintthemultiplespecifiedsheetsfromtheExcelworkbookintoasinglePDFfileandnameitaccordingtothecellvaluesfromasheetwithVBA. VBACodeExplanation OptionExplicit Forcestodeclareallthevariablesexplicitlyofthefile. PublicSubPrintSpecificSheetsToPdfWithRename() Namethesub-procedureofthemacro. DimiSheetListAsVariant DimiSheetAsWorksheet DimiFileNameAsString DimiFilePathAsString Declaresallthevariables. SetiSheet=ThisWorkbook.Sheets("Sheet1") StoresthesheetfromwherewewillbetakingthecellvaluestonameourPDFfile. iSheetList=Array("Sheet1","Sheet2") DeclaresthesheetsthatwillbeexportedintoPDF. iFilePath="C:\ExcelDemy\" DeclaresthedirectorytostoreourPDFfile. WithiSheet iFileName=iFilePath&.Range("B5").Value&""&.Range("C5").Value&"-"&.Range("D5").Value&".pdf" EndWith SelectsthesheetandtakesvaluesfromCellB5,C5andD5tonamethePDFfile.Thenexitsthesheet. ThisWorkbook.Sheets(iSheetList).Select iSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFileName,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=True iSheet.Select SavethehardcodedworksheetsfromtheactiveworkbookasasinglePDFfile– Inthepathaddressandwiththefilename WiththeStandard WiththeWordfileproperties. WithoutthePrintAreas. Willautomaticallyopenafterthecodeexecution. EndSub Endsthesub-procedureofthemacro. ReadMore:HowtoPrintSelectedCellsinExcel(2EasyWays) 5.ApplyMacrotoExportMultipleExcelSheetstoPDFinsideaCreatedFolder Areyouboredofprovidingthefolderpatheverytimeyourunthecode?DoyouwanttosavethePDFfileinsideafolderthatyoucreateviamacrocode?Thenfollowthissectionwithfullconcentration. Steps: Firstly,openVisualBasicEditorfromtheDevelopertabandInsertaModuleinthecodewindow. Secondly,copythefollowingcodeandpasteitintothecodewindow. SubPrintSheetsToPdfInFolder() DimiFolderAdrsAsString iFolderAdrs="C:\ExcelDemy\NewStudentInformation" MkDiriFolderAdrs    Sheets(Array("Sheet1","Sheet2","Sheet3")).Select    ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFolderAdrs&"\StudentInformation",OpenAfterPublish:=False,IgnorePrintAreas:=False MsgBox"Allworksheetsaresuccessfullyexportedintoasinglepdf!" EndSub Yourcodeisnowreadytorun. Afterthat,Runthemacro.Youwillbenotifiedbyapop-upMsgBoxaftersuccessfulfileconversion. Fromthere,justsimplypressOK. Now,beforegoingfurther,let’sdescribeacoupleofthingsfirst.Seetheindicatedpartofthecodeintheimagebelow? Inthatlineofthecode,wedidthetricks.Westorethepath–C:\ExcelDemy–wherewewanttosavethePDFfileandfollowingthatweprovidedaname–“NewStudentInformation”–asthenewfoldernameinourcase.Youmustwritethepathaddressaccordingtoyourdataandyoucanprovideanynamethatyouwantasyournewfoldername. Nowagain,considerthefollowingimage. Inthefirstindicatedpart,westoredthesheetsnamed–Sheet1,Sheet2andSheet3–thatwewanttoexportasPDF.Ifyouwant,thenyoucanaddmoresheetstothatlineorremoveanysheetsfromthatline. Inthesecondindicatedpart,weprovidedanewfilename–“StudentInformation”–forourPDFfile.Youcanwriteanynamethatyouwant. Nowthatyouhaveunderstoodthekeythingsofthecode,let’scheckwhetherthenewlycreatedfolderthatwehardcodedas“NewStudentInformation”intheC:\ExcelDemypath,iscreatedornot. Asyoucanseefromthepicturebelow,wehavethenewlycreatedfolder“NewStudentInformation”inthe“ExcelDemy”folderinDriveC. AswehavesuccessfullycreatedafoldertostoreourPDFfile,nowit’stimetocheckwhetherthefolderhasthefileinitornot. Openthefolder. Andasyoucanseefromthepicturebelow,wehavethenewlycreatedPDFfile“StudentInformation”insidethe“NewStudentInformation”folderthatresidesinthe“ExcelDemy”folderinDriveC. Let’sopenthePDFfileandcheckwhetherithasallthesheetsthatweprovidedinthecodeinitornot. Whenweopenthefile,“StudentInformation”,wesawthatithasthreepageswhereeachpagecarriesthedatafromeachoftheworksheetsfromtheExcelworkbook.Page1ofthePDFfileholdsthedatafromSheet1fromtheworkbook,Page2holdsthedatafromSheet2andPage3holdsthedatafromSheet3fromtheworkbook. Now,toprintthePDFfile,youhavetofollowthestepsbelow. First,openthefile. Then,gotoFile->Print.Alternatively,youcanpressCtrl+Pfromyourkeyboardtoprintthefile. ItwillopenthePrintIfyouwant,thenyoucanmodifythepagesetupaccordingtoyourneed.Then,pressPrint. YournewlycreatedPDFfilefrommultiplehardcodedExcelsheetsinsidethenewfolderwillstartprinting.ThisishowyouprintthemultiplesheetsfromtheExcelworkbookintoasinglePDFfileandstoreitinsideafolderthatyoucreatewithVBA. VBACodeExplanation SubPrintSheetsToPdfInFolder() Firstly,provideanameforthesub-procedureofthemacro. DimiFolderAdrsAsString Declarethevariabletocarrythepathaddress. iFolderAdrs="C:\ExcelDemy\NewStudentInformation" Storesthedirectoryanddeclaresanewfoldername. MkDiriFolderAdrs CreatesthenewfolderwithExcel’sMkDirstatement. Sheets(Array("Sheet1","Sheet2","Sheet3")).Select ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iFolderAdrs&"\StudentInformation",OpenAfterPublish:=False,IgnorePrintAreas:=False SelectsthesheetsandsavethemasasinglePDFfile– Inthepathaddressandwiththefilename–“\StudentInformation”–provided. WithoutthePrintAreas. Turnofftheautomaticfileopeningafterthecodeexecution. MsgBox"Allworksheetsaresuccessfullyexportedintoasinglepdf!" Confirmationmessageaftersuccessfullyfilecreation. EndSub Endsthesub-procedureofthemacro. RelatedContent:HowtoPrintExcelSheetwithHeaderonEveryPageinExcel(3Methods) 6.ImplementVBAtoExportActiveWorksheettoPDFMultipleTimesintheWorkingFolder ThissectionwillteachyouhowyoucansavetheactiveExcelworksheetintoasinglePDFfilemultipletimeswithdifferentnameswithVBA. Thestepstoexecutethataregivenbelow. Steps: Atfirst,openVisualBasicEditorfromtheDevelopertabandInsertaModuleinthecodewindow. Afterthat,copythefollowingcodeandpasteitintothecodewindow. SubPrintSpecificSheetsToPdfInCurrentPath() DimiSheetAsWorksheet DimiBookAsWorkbook DimiFileNameAsString DimiFilePathAsString DimiFileAsString DimiPathFileAsString DimNewFileAsVariant DimmsgAsLong OnErrorGoToerrHandler SetiBook=ActiveWorkbook SetiSheet=ActiveSheet iFilePath=iBook.Path IfiFilePath=""Then  iFilePath=Application.DefaultFilePath EndIf iFilePath=iFilePath&"\" iFileName=iSheet.Range("B6").Value&"-"&iSheet.Range("C6").Value&"-"&iSheet.Range("D6").Value iFile=iFileName&".pdf" iPathFile=iFilePath&iFile IfiOldFile(iPathFile)Then  msg=MsgBox("Replacecurrentfile?",vbQuestion+vbYesNo,"ExistingFile!")  Ifmsg<>vbYesThen    NewFile=Application.GetSaveAsFilename(InitialFileName:=iPathFile,FileFilter:="PDFFiles(*.pdf),*.pdf",Title:="Enterfolderandfilenametosave")    IfNewFile<>"False"Then      iPathFile=NewFile    Else      GoToexitHandler    EndIf  EndIf EndIf iSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iPathFile,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=False MsgBox"NewPDFfileiscreated:"&vbCrLf&iPathFile exitHandler:    ExitSub errHandler:    MsgBox"ThereisanerrorwhilecreatingPDFfile!"    ResumeexitHandler EndSub FunctioniOldFile(rsFullPathAsString)AsBoolean  iOldFile=CBool(Len(Dir$(rsFullPath))>0) EndFunction Yourcodeisnowreadytorun. Then,RunthemacroandthefilepathandthenameforyournewlycreatedPDFfilewillbeshowntoyouviaExcelMsgBox. Tounderstandhowwegotthenameandthefilepath,youhavetolookatthefollowingimage. Seetheindicatedpartintheaboveimage.Thisdidthetrickofnamingthefile. Inthispartofthecode,weprovidedthreecellnumbers–CellB6,C6andD6–fromtheactivesheet,Sheet1.CellB6hasthevalue“RomanReigns”,CellC6hasthevalue“102”andCellD6hasthevalue“56”.So,thenameforthenewlycreatedPDFfileis“RomanReigns–102–56”,wealsoprovidedahyphen(-)betweenCellB6,C6andD6inthecode. ThepathwegotisnoneotherthanthepathwherewehavethesourceExcelworkbook.WehaveourworkbookintheC:\ExcelDemypath,sothenewlycreatedPDFfile“RomanReigns–102–56”isalsostoredintheexactsameaddress. Now,Runthemacro. Now,let’scheckwhetherthepathhasthefileinitornot. Asyoucanseefromthepicturebelow,thesourceExcelfile–“PrintMultipleSheetstoSinglePDF”–andthenewlycreatedPDFfilefromit–“RomanReigns–102–56”–bothareinthe“ExcelDemy”folderinDriveC. Ifyouopenthefile,youwillseethatthePDFhasonlyonepageandthedataonthatpageistheexactdatafromtheactivesheet,Sheet1fromtheworkbook. Now,wewanttoprintthefile,right?ToprintthePDFfile,youhavetofollowthestepsbelow. First,openthefile. Then,gotoFile->Print.Alternatively,youcanpressCtrl+Pfromyourkeyboardtoprintthefile. ItwillopenthePrintIfyouwant,thenyoucanmodifythepagesetupaccordingtoyourneed.Then,pressPrint. Now,whatifyouneedtomodifytheExcelsheetandre-printitagain?WhatifyouwanttokeepboththeoldPDFfileandthenewPDFfile? Runthecodeagain. Therewillbeapop-upMsgBoxaskingyouwhetheryouwanttoreplacetheoldfilewiththenewfileornot. Ifyouwanttoreplacetheoldfile,thensimplyclickYes. ItwillautomaticallyreplaceyouroldPDFfilecreatedfromtheExcelworksheetwiththenewone. ButifyouwanttokeepbothPDFfiles,thenclickNOinthepop-upMsgBox. ThiswilltakeyoutotheBrowsewindow,whereyouwillbeabletoselectthepathandrenamethefileaccordingtoyourrequirements.Inourcase,westorethenewPDFfileintheC:\ExcelDemypathandrenamedthefile“RomanReigns–102–56Part2”. ClickSave. NowgobacktothefiledirectorytocheckwhethertherearemultiplecopiesofthesamePDFfilewithdifferentnamesornot. Asyoucanseefromthepicturebelow,bothfiles–“RomanReigns–102–56”and“RomanReigns–102–56Part2”–areinthe“ExcelDemy”folderinDriveC. ThisishowyouprintanactiveExcelsheettoaPDFandrenameitmultipletimeswithVBA. VBACodeExplanation SubPrintSpecificSheetsToPdfInCurrentPath() Atfirst,namethesub-procedureofthemacro. DimiSheetAsWorksheet DimiBookAsWorkbook DimiFileNameAsString DimiFilePathAsString DimiFileAsString DimiPathFileAsString DimNewFileAsVariant DimmsgAsLong Then,declarethevariables. OnErrorGoToerrHandler Ifanyerroroccurs,thengotoerrHandlerstatement. SetiBook=ActiveWorkbook SetiSheet=ActiveSheet Later,storetheactiveworkbookandworksheetinthevariables. iFilePath=iBook.Path IfiFilePath=""Then iFilePath=Application.DefaultFilePath EndIf iFilePath=iFilePath&"\" Getstheactiveworkbookfolderifthefileissaved. iFileName=iSheet.Range("B6").Value&"-"&iSheet.Range("C6").Value&"-"&iSheet.Range("D6").Value Afterthat,ittakesvaluesfromCellB6,C6andD6tonamethePDFfileandstorethenameinthevariable. iFile=iFileName&".pdf" iPathFile=iFilePath&iFile Later,createsadefaultnameforthenewfiletosave. IfiOldFile(iPathFile)Then msg=MsgBox("Replacecurrentfile?",vbQuestion+vbYesNo,"ExistingFile!") Ifmsg<>vbYesThen NewFile=Application.GetSaveAsFilename(InitialFileName:=iPathFile,FileFilter:="PDFFiles(*.pdf),*.pdf",Title:="Enterfolderandfilenametosave") IfNewFile<>"False"Then iPathFile=NewFile Afterthat,itperformsthefilereplacementoperation.Ifthefilealreadyexists,thensavethefilewithanewpathandname.Ifthefiledoesn’texist,thensavethefileinthecurrentdirectory. Else GoToexitHandler EndIf EndIf EndIf Otherwise,gototheexitHandlerstatementinthecode. iSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=iPathFile,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=False SavetheworksheetfromtheactiveworkbookasasinglePDFfile– Inthepathaddressandwiththefilename WiththeStandard WiththeWordfileproperties. WithoutthePrintAreas. Turnofftheautomaticfileopeningafterthecodeexecution. MsgBox"NewPDFfileiscreated:"&vbCrLf&iPathFile Confirmationmessageaftersuccessfullyfilecreation. exitHandler: ExitSub CodeintheexitHandlerstatement,toexitthesub-procedure. errHandler: MsgBox"ThereisanerrorwhilecreatingPDFfile!" ResumeexitHandler CodeintheerrHandlerstatement,tonotifythatthereisanerror.GobacktotheexitHandlerstatement. EndSub Finally,endsthesub-procedureofthemacro. FunctioniOldFile(rsFullPathAsString)AsBoolean iOldFile=CBool(Len(Dir$(rsFullPath))>0) EndFunction Lastly,callsthefunctiontoexecutealltheoperationsthatwejustexplained. ReadMore:HowtoPrintExcelSpreadsheetonMultiplePages(3Ways) Conclusion Toconclude,thisarticleshowedyou6differentcriteriaonhowtoprintmultipleExcelsheetstoasinglePDFwiththeVBAmacro.Ihopethisarticlehasbeenverybeneficialtoyou.Feelfreetoaskanyquestionsregardingthetopic. RelatedArticles HowtoPrintPageNumberinExcel(5EasyWays) ChangePrintAreainExcel(5Methods) HowtoPrintGraphinExcel(5Ways) PrintExcelSheetwithLines(3EasyWays) HowtoKeepHeaderinExcelWhenPrinting(3Ways) SaveSavedRemoved0 Tags:HowtoPrintExcelSheet SanjidaAhmed HelloWorld!ThisisSanjida,anEngineerwhoispassionateaboutresearchingreal-worldproblemsandinventingsolutionsthathaven’tbeendiscoveredyet.Here,ItrytodelivertheresultswithexplanationsofExcel-relatedproblems,wheremostofmyinterpretationswillbeprovidedtoyouintheformofVisualBasicforApplications(VBA)programminglanguage.Beingaprogrammerandaconstantsolutionseeker,mademeinterestedinassistingtheworldwithtop-notchinnovationsandevaluationsofdataanalysis. RelatedArticles AddedtowishlistRemovedfromwishlist0 [Solved]:UserDefinedTypeNotDefinedinExcelVBA(2QuickSolutions) AddedtowishlistRemovedfromwishlist0 HowtoUseMacrotoCleanUpDatainExcel(4EasyMethods) AddedtowishlistRemovedfromwishlist0 ExcelVBA:CopyCellValueandPastetoAnotherCell AddedtowishlistRemovedfromwishlist0 ExcelVBA:CopyRangetoAnotherWorkbook Wewillbehappytohearyourthoughts LeaveareplyCancelreply Δ AffiliateDisclosure Thispostmaycontainaffiliatelinks,meaningwhenyouclickthelinksandmakeapurchase,wemayearnanaffiliatecommission,butthisneverinfluencesouropinion. 100+Pre-BuiltExcelTemplates AboutExcelDemy.com ExcelDemyisaplacewhereyoucanlearnExcel,DataAnalysis,andotherOfficerelatedprograms.Weprovidetips,howtoguideandalsoprovideExcelsolutionstoyourbusinessproblems. ExcelDemy.comisaparticipantintheAmazonServicesLLCAssociatesProgram,anaffiliateadvertisingprogram.IearnasmallcommissionifyoubuyanyproductsusingmyaffiliatelinkstoAmazon. Contact|PrivacyPolicy CategoriesCategories SelectCategory AdvancedExcel DataAnalysiswithExcel ExcelBasics ExcelBooks ExcelCharts ExcelforFinance ExcelFormulas ExcelFunctions ExcelPivotTable ExcelPowerQuery ExcelSolver ExcelTemplates ExcelTraining&Courses Macros&ExcelVBA Software Uncategorized SearchinExcelDemy.com