How to save specific multiple worksheets to a pdf file ...

Confirm selected sheets by press with left mouse button oning "Yes". save multiple excel sheets to a single pdf file1 · A dialog box appears, ... GetDigitalHelp More Home Functions Formulas Features ExcelBasics ExcelDefinedTable AdvancedFilter DataValidation Drop-downlists NamedRanges Solver Charts Conditionalformatting Pivottable VBA VBAFunctions Methods Properties Statements Macros Userdefinedfunctions Files&folders Checkboxes Archive Allarticlessortedbydate Categories Products AdvancedExcelCourse Contact Aboutme BlogsIread VBforApplicationsFunctionsArrayFixJoinLbounduboundSgnSplitMethodsEvaluateRange.findTexttocolumnsPropertiesStatementsDoloopFornextGotoSelectcaseSetThenelseelseifendWithendwithVBAArrayvariablesBoxesComboboxesDialogboxesFilesLikeoperatorListboxesMacroCopyn-throw Copyfromworkbooks Non-contiguousrngs Selectblankcells Quickaccesstoolbar Runmacrowhenopen Howto-Dialogboxes Multiplynumbersineachrowwithremainingrowsincellrange(UDF) HowtosavespecificmultipleworksheetstoapdffileprogrammaticallyFindliteralvaluesinformulas Consecutivevalues Howtocreatealistofcommentsfromaworksheetprogrammatically Copyfilteredtables Howtohighlightrowoftheselectedcellprogrammatically Addmacrotoribbon Textboxes Show/Hideimage[VBA] Togglehiddensheets Togglehiddencolumn Scrollbar Daterangesoverlap Counttextstringinallformulasinaworksheet[VBA] Locateashapeinaworkbook WorkingwithFILES Changeapic Moveashape[VBA] Macro-Dropdown Hidespecificcolumns Copyworksheetsinactiveworkbooktonewworkbooks Copytablecriteria Excelcalendar[VBA] Extractcellreferences Sortcelldelimiter[VBA] Quicklycreatesheets Clicktoselectseries SortExceltable[VBA] Splitvalues Searchrelatedtables Createcommentifcellvalueislargerthancolumn Selectacell Gettingthingsdone Normalizedata[VBA] Addvaluestosheets Addvaluestotable Addvaluetodropdown OpenExcelfiles Buttonon/offmacro Automatedataentry Datavalidationlist LogWBactivities Autoresizecolumns Copyexpandingrange Listopenworkbooks Basicdataentry[VBA] ListExceltables Comboboxes Dropdownlist FilterExcelTable Populatecombobox(2) Drop-downlist[VBA] FilterTable[VBA] Populatelistbox(2) Populatecombobox Additem-contextmenu SelectA1onallsheets SavetoAdd-In Grouptext/Text-to-cols CreateaPrintbutton Select/Viewinvoice Populatelistbox Editinvoicedata[VBA] Saveinvoicedata[VBA] Consolidatesheets Rearrangedata Splitdataacrosssheets Schedulecalendar Uniquedist.list Missingnumbers UserdefinedfunctionTextbetweenwords Searchforfileinfolders Splitstrings&search Commaseparatedvals Countcellcolor Replacetextstrings Lookupmergedvalues Displayvalsbasedrng Extractfilteredtable Reorganizedata[UDF] Two-waylookup SUMIFacrosssheets Listfilesinfolders SavetoAdd-In Uniq.distreccasesens Uniq.dist.casesens. Wordfrequency[UDF] Fuzzylookups[UDF] Wordscontainingstring Nonetclosetozero Findnumbersinsum Permutw/orepetition Permutwithrepetition Valuesnotshared Sharedvalues Extractduplicatevalues Countuniquedist.vals Uniquestrings[UDF] Uniquedistinctstrings Splitduplicatestrings Splitstrings Groupvaluesequally FilesandfoldersCopyfromworkbooks Searchforfileinfolders Editfilenames Unzipfiles Filterduplicatefiles Comparefilenames Searchworkbooks(1) Movedatatoworkbooks Searchworkbooks(0) Filepassw.protected WorkingwithFILES Filenamehyperlinks OpenExcelfiles Listfilesinfolders CheckboxesMulti-levelTo-Dolist OpenExcelfiles Buttonon/offmacro Copycheckboxes2/2 Addcheckboxes1/2 Sumusingcheckboxes Howtosavespecificmultipleworksheetstoapdffileprogrammatically Author:OscarCronquistArticlelastupdatedonApril14,2021 Thisarticledemonstratesmacrosthatsaveworksheetstoasinglepdffile. What'sonthiswebpage ExportallworksheetsinworkbooktoPDFprogrammatically Howtosavespecificmultipleworksheetstoapdffileprogrammatically Howtousethemacro? VBAcode Wheretoputthecode? GetExcelfile 1.ExportallworksheetsinworkbooktoPDFprogrammatically Thefollowingmacrosavesallworksheetsintheactiveworkbooktoasinglepdffile. 'Namemacro SubExportWbtoPdf() 'Selectallworksheetsinactiveworkbook ForEachWSInActiveWorkbook.Worksheets Worksheets(WS.Name).SelectFalse NextWS 'Askforadirectorytosavethepdffilein WithApplication.FileDialog(msoFileDialogFolderPicker) .Show myfolder=.SelectedItems(1)&"\" EndWith 'Askforasavefilenameforthepdffile myfile=InputBox("Enterfilename","Saveas..") 'Saveallworksheetsinworkbooktopdffile ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=_ myfolder&myfile_ ,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas_ :=False,OpenAfterPublish:=True EndSub Wheretoputthecode? Backtotop 2.Howtosavespecificmultipleworksheetstoapdffileprogrammatically 2.1Howtousethemacro? Makesureyouselecttheworksheetsyouwanttosaveasasinglepdffilebeforeyourunthemacro.ToselectmultipleworksheetspressandholdCTRLandthenpresswithleftmousebuttononwiththemouseontheworksheettabslocatedatthebottomofyourExcelscreen. Thishappenswhenyoustart themacro Confirmselectedsheetsbypresswithleftmousebuttononing"Yes". Adialogboxappears,chooseasavefolder.PresswithleftmousebuttononOK. Anewdialogboxappears.Enterafilename. PresswithleftmousebuttononOKbutton. Apdffileiscreatedandopensautomatically. Backtotop 2.2VBAcode SubSaveSelectedSheetsToPDF() DimstrAsString,myfolderAsString,myfileAsString str="Doyouwanttosavethesesheetstoasinglepdffile?"& Chr(10) ForEachshtInActiveWindow.SelectedSheets str=str&sht.Name&Chr(10) Nextsht answer=MsgBox(str,vbYesNo,"Continuewithsave?") Ifanswer=vbNoThenExitSub WithApplication.FileDialog(msoFileDialogFolderPicker) .Show myfolder=.SelectedItems(1)&"\" EndWith myfile=InputBox("Enterfilename","Saveas..") ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=_ myfolder&myfile_ ,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas_ :=False,OpenAfterPublish:=True EndSub Backtotop 3.WheredoIputthecodeinmyworkbook? StarttheVisualBasicEditor(Alt+F11). Presswithleftmousebuttonon"Insert"onthetopmenu,seeimageabove. Presswithleftmousebuttonon"Module". Pastecodetowindow. ReturntoExcel. Note,saveyourworkbookwithfileextension*.xlsm(macro-enabledworkbook)toattachthecode. Backtotop Explainingthemacro AmacrostartswithSubandthenthemacroname Sub SaveSelectedSheetsToPDF() Declaringvariables Variablesstr,myfolderandmyfile aredeclareddatatypeString. Readmoreabout Definingdatatypes. DimstrAsString,myfolderAsString,myfileAsString Concatenatetextstrings  Theselectedsheetnamesareconcatenatedwithaquestion,Chr(10)isacarriagereturn. str="Doyouwanttosavethesesheetstoasinglepdffile?"& Chr(10) ForEachshtInActiveWindow.SelectedSheets str=str&sht.Name&Chr(10) Nextsht Messagebox   Themessageboxletsyouconfirmyouhaveselectedthecorrectsheets.Iftheanswerisnothemacroends. answer=MsgBox(str,vbYesNo,"Continuewithsave?") Ifanswer=vbNoThenExitSub Chooseadirectory Thedirectoryissavedinthestringvariablemyfolder. WithApplication.FileDialog(msoFileDialogFolderPicker) .Show myfolder=.SelectedItems(1)&"\" EndWith Enterafilename Thefilenameissavedinthestringvariablemyfile. myfile=InputBox("Enterfilename","Saveas..") Createpdf Theselectedsheetsaresavedasapdffilewiththenamemyfileandpathmyfolder. ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=_ myfolder&myfile_ ,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas_ :=False,OpenAfterPublish:=True Endamacro Amacroendswith thisstatement. EndSub Backtotop Assignmacro toabuttonforquickaccess Gototab"Developer"ontheribbon Presswithleftmousebuttonon"Insert"button Presswithleftmousebuttonon"Button(FormControl) Presswithleftmousebuttononanddragonsheettobuildabutton Assignmacro SaveSelectedSheetsToPDF PresswithleftmousebuttononOK Backtotop Savethemacroinyourpersonalmacroworkbook Ifyousavethemacroinapersonalmacroworkbook,youcanaccess that macronomatterwhatworkbookyouhaveopen. Readthis: CopyyourmacrostoaPersonalMacroWorkbook Backtotop GettheExcelfile Export-all-worksheets-in-workbook-to-PDF-programmaticallyv3.xlsm Backtotop Macrocategory Saveinvoicedata[VBA]Thisarticledemonstratesamacrothatcopiesvaluesbetweensheets.Iamusingtheinvoicetemplateworkbook.Thismacrocopies[…] OpenExcelfilesinafolder[VBA]Thistutorialshowsyouhowtolistexcelfilesinaspecificfolderandcreateadjacentcheckboxes,usingVBA.The[…] Splitdataacrossmultiplesheets[VBA]InthispostIamgoingtoshowhowtocreateanewsheetforeachairplaneusingvba.The[…] WorkingwithCOMBOBOXES[FormControls]Thisblogpostdemonstrateshowtocreate,populateandchangecomboboxes(formcontrol)programmatically.Formcontrolsarenotasflexible[…] IdentifymissingnumbersinacolumnTheimageaboveshowsanarrayformulaincellD6thatextractsmissingnumbersicellrangeB3:B7,thelower[…] Excelcalendar[VBA]Thisworkbookcontainstwoworksheets,oneworksheetshowsacalendarandtheotherworksheetisusedtostoreevents.The[…] RunaMacrofromaDropDownlist[VBA]ThisarticledemonstrateshowtorunaVBAmacrousingaDropDownlist.TheDropDownlistcontainstwo[…] ChangechartdatarangeusingaDropDownList[VBA]InthisarticleIwilldemonstratehowtoquicklychangechartdatarangeutilizingacombobox(drop-downlist).Theabove[…] WorkingwithFILESWhat'sonthispageCopyafileCopyandrenameafileRenameafileListfilesinafolder[…] AutoresizecolumnsasyoutypeExceldoesnotresizecolumnsasyoutypebydefaultastheimageabovedemonstrates.Youcaneasilyresizeall[…] HowtocreateaninteractiveExcelchart[VBA]Thisarticledescribeshowtocreateaninteractivechart,theusermaypresswithleftmousebuttononabutton[…] CreateaPrintbutton[VBA]ThisarticledescribeshowtocreateabuttonandplaceitonanExcelworksheetthenassignamacroto[…] Howtochangeapictureinaworksheetdynamically[VBA]Rahulasks:Iwanttoknowhowtocreateavlookupsheet,andwhenweenteranameina[…] CreateauniquedistinctlistusingAdvancedFilterinamacro[VBA]Question:hiall,thanksforthegreatformula/arrayformula.itworksgreat.lately,inoticedthatthearrayformulawill[…] Exceltemplate:GettingThingsDone[VBA]Inthisarticle,Iamgoingtodemonstrateasimpleworkbookwhereyoucancreateordeleteprojectsandadd[…] Show/hideapictureusingabuttonThisarticleexplainshowtohideaspecificimageinExcelusingashapeasabutton.Iftheuser[…] Createnewworksheetsprogrammaticallybasedonvaluesinacellrange[VBA]Thisarticledemonstratesamacrothatinsertsnewworksheetsbasedonnamesinacellrange.Thecellrangemay[…] HowtohighlightrowoftheselectedcellprogrammaticallyTodayIwouldliketosharewithyouthesesmalleventhandlerproceduresthatmakeiteasierforyouto[…] Findcellscontainingformulaswithliteral(hardcoded)valuesThisarticledescribesdifferentwaystolocateliteralorhardcodedvaluesinformulas.Theimageaboveshowstheresultfrom[…] Morethan600Excelformulas ExcelcategoriesFunctionsFormulasExcelTablesAdvancedFilterDataValidationDropDownNamedRangesExcelSolverChartsConditonalFormattingPivotTablesVBAMacrosUDFsArchive Comments(12) 12Responsesto“Howtosavespecificmultipleworksheetstoapdffileprogrammatically” BertvanZandbergensays: January28,2015at6:36pm HiOscar FirstofallthanksforyourbeautifulmacrotocopyanExcelfiletoPDF.IfyouwanttocopyawidehorizontalfieldtoPDF,itcancauseasplitinthePDF:yougetdividedthetext,graphicsand/orobjectontwopages).That'snotwhatyouwant! Tomakeaperfecthorizontallayout,Irecommendtodefinefirstintheworksheet:PrintRange,PageLayout(onribbon).CreatewithFile/PrintOptionsothernecessaryinstructions,forexample,choosesettingslike"printtofit",bordersetc.etc.Theactualprintlayoutisdisplayedontherightside. Insteadofanormalprint,youcannowactivatetheMacro.Thesesimpleadjustmentsdon'tgiveadistributionontwopages,butanappropriatehorizontalPDFformat.So,it'sacontributiontoyourreaders. Bestregards, BertvanZandbergen Reply Oscarsays: January29,2015at8:24am BertvanZandbergen, thankyouforcommenting. testradsays: July1,2015at3:29pm Hi,Oscar yourcodeworksgreatbutitonlyprintsexcelsheetsthathavebeenindividuallyselectedbeforerunningthemacro.isthereawaytogetthecodetoautomaticallyselectallunhiddensheetsinexcelandthenprinttopdf. Thereasonwhyisayunhiddensheetsis,becauseihavealargeuserformthatbasedonuserselectionshidesunneededexcelsheets.soiwouldonlyneedtoprinttherelevantsheets,whichwouldbetheunhiddensheets. thanksforyourtime, TylerEstrada Reply Mohamedsays: February1,2016at4:44pm HiOscar thanksforthegreateffort onequestion ifIwanttoexporteachsheetasaseparatepdffile,whatshouldbechangedinthecode? Thanks Mohamed Reply Lucsays: February26,2016at12:14pm Hi, Ijustfoundtheerror:Ialsoopenedthexlsmfile,hadalookatthecodethere,andfoundthatitdiffersfromthecodeatthetopofthisarticle(Icopiedthecodefromtherefirst). Thedifferencewasinthe"amp;amp;amp;amp"portion. Afterremovingthese,themacroisrunningsmoothnow. Cheers Luc Reply Joesays: February26,2016at6:41pm Oscar-Many,manythksforthis,itwasanexcellentpost,andagreatsolutionforwhatIwaslookingfor.Onequestion,Iwaswonderinghowtodothisbasedonavalidationlist.Forexample,letssayIhave10sheets.IhaveadropdownlistsayinA1ofSheet1,whichtheworkbookmenu.Thedropdownlistcontains-saytworeports-ShortReportandLongReport. IfIpresswithleftmousebuttononShortReport,itwillprintpages-2,3,and5.IfIselectLongReport,itwillprintpages2,6,7,8,9,and10. IwouldliketohavethesameoptionforasinglePDFfile.Thus,ifIselectShortReport,pages2,3and5willbesavedinasinglePDFfile.Anyassistanceonthiswouldbegreatlyappreciated.cheers Joe Reply MANOJsays: September12,2018at1:12pm DearSir, whenirunthemacroitsshowing"runtimeerror5".invalidprocedureorcallargument. pleasehelpustosolvedtheissues. Thanks Manoj Reply Oscarsays: September13,2018at8:49am MANOJ, WordPresshasaddedcharactersthatshouldn'tbeinthecode,Ihavetriedtoremovethem.Irecommendyoutryouttheattachedfile. MANOJsays: September14,2018at8:51am DearSir, Youhaverecommendedtogettheattachedfilebutthatisnotreflecting,isitpossiblesendmethroughmail([email protected]) Regards Manoj MANOJsays: September18,2018at7:15am DearSir, Pleasegothroughthebelowcodeandhelpmetosolvetheissuesthatwhenirunthemacroitsshowingruntimeerror5".invalidprocedureorcallargument. Subsaveinvoice() Sheet2.Range("a1:l57").ExportAsFixedFormatxlTypePDF,Filename:="C:\Users\USER\Desktop\inventory\"&Sheet2.Range("j11").Value,openafterpublish:=True Thanks Manoj EndSub Reply hussainassanisays: November25,2019at7:12pm Howcanyouchoosewhichsheetisprintedfirstandwhichoneisprintednext? Reply Oscarsays: December6,2019at2:12pm hussainassani, Youneedtorearrangetheworksheettabstobeabletoprinttheminagivenorder. Tosorttheworksheetsinagivenorderyoucanpresswiththeleftmousebuttonandholdonaworksheettabanddragtothepositionyouwant,seethisguide: https://www.extendoffice.com/documents/excel/3410-excel-change-orders-of-tabs.html#a1 LeaveaReply Clickheretocancelreply. Name(required) Mail(willnotbepublished)(required) Website Δ Howtocomment Howtoaddaformulatoyourcomment Insertyourformulahere. Convertlessthanandlargerthansigns Usehtmlcharacterentitiesinsteadoflessthanandlargerthansigns. becomes> HowtoaddVBAcodetoyourcomment [vb1="vbnet"language=","] PutyourVBAcodehere. [/vb] Howtoaddapicturetoyourcomment: Uploadpicturetopostimage.orgorimgur Pasteimagelinktoyourcomment. ContactOscar Youcancontactmethroughthiscontactform Formulacategories Count Dates Extract IndexandMatch Logic Lookups Stringmanipulation Sum Time Vlookup Searchwebsite PrivacyPolicy