Excel VBA: to export selected sheets to a single ... - Super User

What errors are you getting when running this macro? · I do not get any errors, but the pdf-file includes the first sheet only, and not the other ... Resultsfromthe2022DeveloperSurveyarenowavailable SuperUserisaquestionandanswersiteforcomputerenthusiastsandpowerusers.Itonlytakesaminutetosignup. Signuptojointhiscommunity Anybodycanaskaquestion Anybodycananswer Thebestanswersarevotedupandrisetothetop Home Public Questions Tags Users Companies Unanswered Teams StackOverflowforTeams –Startcollaboratingandsharingorganizationalknowledge. CreateafreeTeam WhyTeams? Teams CreatefreeTeam Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. Learnmore ExcelVBA:toexportselectedsheetstoasinglepdf,Exceldoesn'texportotherthanfirstsheet AskQuestion Asked 2years,5monthsago Modified 2years,5monthsago Viewed 996times 1 I'vetriedseveralmethodstoincludeaselectionofsheets,andthenexporttopdf.Eachtimeonlythefirstsheetturnsouttobeincludedinthepdf-file. WhatI'mtryingtoachieveis: 1.Selectsheetsmanually 2.Runthemacro.Themacrowill:1.Exporttheselectedsheetstoasinglepdf.2.Deselecttheselectedsheets(tolateravoidunintentionallyeditingacrosstheselectedsheets.) SubExport_Selected_Sheets_To_PDF() DimwsAsWorksheet DimPDF_Name,Doc_ID,Excel_Name,SelectedSheets()AsString Dimn,iAsLong Dimfso:Setfso=CreateObject("Scripting.FileSystemObject") 'CodefornamingthePDF(equalsexcelfilenameincludingaprefixfromacellnamed"DocID") 'Willbesavedatsamelocationastheexcel-file Doc_ID=Range("DocID").Value'DocID=namedcellintheWS Excel_Name=fso.GetBaseName(ActiveWorkbook.FullName)'Excelfilenamewithoutfileextension PDF_Name=Doc_ID&"_"&Excel_Name&".pdf"'PDFfilename 'ShowingthePDF_Name Debug.PrintPDF_Name 'Codeforselectingthe"selected"sheets n=0 ForEachwsInActiveWindow.SelectedSheets ReDimPreserveSelectedSheets(n) SelectedSheets(n)=ws.Name n=n+1 Next 'Showingthelistofselectedsheets Fori=LBound(SelectedSheets)ToUBound(SelectedSheets) Debug.PrintSelectedSheets(i) Nexti 'Exportselectedsheetstopdf Sheets(SelectedSheets).Select ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=PDF_Name,Quality:=_ xlQualityStandard,IncludeDocProperties:=False,IgnorePrintAreas:=False,_ OpenAfterPublish:=True 'Deactivateselectiontoavoideditingacrossselectedsheets. ActiveSheet.Select EndSub microsoft-excelvba Share Improvethisquestion Follow editedJan12,2020at13:51 LeifBottolfs askedJan12,2020at13:44 LeifBottolfsLeifBottolfs 2555bronzebadges 3 Whaterrorsareyougettingwhenrunningthismacro?Onwhichline?Haveyoutriedusingdebugtostepthroughthemacrocommand-by-command? – harrymc Jan12,2020at14:06 Idonotgetanyerrors,butthepdf-fileincludesthefirstsheetonly,andnottheothersheetsthatareselected.Seemstomethatthecodedoesn'tpayattentionto"Sheets(SelectedSheets).Select",but"ActiveSheet"only. – LeifBottolfs Jan12,2020at14:12 Solved:Themacrowillworkiftheselectedsheetshavedefined"Print_Areas".Ifnotthemacrohastoincludesomelinesdefiningtheprint_areasineachsheet(asinanswer-post). – LeifBottolfs Jan13,2020at18:51 Addacomment  |  1Answer 1 Sortedby: Resettodefault Highestscore(default) Datemodified(newestfirst) Datecreated(oldestfirst) 1 Inthepost ExcelVBAtoExportSelectedSheetstoPDF wasproposedthesamemethodasyours. However,furthertestinghasshownthatthistechniquedependsonthegroupof cellsbeingselectedoneachworksheet. Thecodewasthenmodifiedtosomethinglike: SubMacro1() Sheets("Sheet1").Activate ActiveSheet.UsedRange.Select Sheets("Sheet2").Activate ActiveSheet.UsedRange.Select Sheets("Sheet3").Activate ActiveSheet.UsedRange.Select ThisWorkbook.Sheets(Array("Sheet1","Sheet2","Sheet3")).Select Selection.ExportAsFixedFormatType:=xlTypePDF,Filename:=_ "C:\Users\James\Desktop\pdfmaker.pdf",Quality:=xlQualityStandard,_ IncludeDocProperties:=True,IgnorePrintAreas:=False,OpenAfterPublish:=_ True EndSub Thisanswerwashighlyupvoted,somighthelpinyourcase. Share Improvethisanswer Follow answeredJan12,2020at15:58 harrymcharrymc 407k2828goldbadges464464silverbadges825825bronzebadges 1 Itcertainlyhelpedingettingtounderstandtheproblem.Theoriginalmacrodidn'tworkbecauseonlythefirstsheethadadefined"Print_Area".ItworkedafterIhadsetprintareasintheothersheets. – LeifBottolfs Jan13,2020at18:47 Addacomment  |  YourAnswer ThanksforcontributingananswertoSuperUser!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy Nottheansweryou'relookingfor?Browseotherquestionstaggedmicrosoft-excelvbaoraskyourownquestion. TheOverflowBlog CelebratingtheStackExchangesitesthatturnedtenyearsoldinSpring2022  GitHubCopilotishere.Butwhat’stheprice?(Ep.457) FeaturedonMeta Testingnewtrafficmanagementtool Duplicatedvotesarebeingcleanedup Related 2 excelvba:toexportmultipleexcelsheetintoitsdesignatedjpgfile 0 Sheetnamedynamicallyassignedfromacellvalue 1 I'mlookingtoinsertmultipleitemizedobjectsinexcelviaaVBA 0 ExcelmacrostoppedworkingafterWindowsupdatekb4103729 1 CopyingdatafrommultiplesheetsintosinglesheetusingVBAwherethedataiscopiedintodifferentrows 2 Exceldoesn'tprintallactivesheetstoPDF 0 MacroorVBAtoselectfirstemptycellinacolumninvokedfromapopupwith3options,Macro1,Macro2orcancel 0 ExportExcelWorksheets(fromlistofsheetnames)tosepareteexcelfilewithVBA HotNetworkQuestions Fullcoveragefenderswithoutchainstaybridge? Definitionsofdeterminantbyuniquefeatures What'sthetypicaltemperatureofareactorcoreinanuclearthermalrocket? Whyhadn'tthewestarrestedRussianbillionairesforcorruption/moneylaunderingpriortotheUkrainewar? Docountries(withruleoflaw)existwhereacourtcanrequirethelegislaturetovoteonalawinordertoclearambiguities? Howwouldthefightingofmedievalstylewarsbealteredbythepresenceofapredatorspecies? FixmyFizzBuzz Non-asciicharactersinRewriteRule:alpha: WhydidtheFirstAmendmentnotoverridetheCopyrightClause? Whywouldanationrequireimmigrantstofulfiladecadeofmilitaryservicetogaincitizenship? forestaligntexttocenterwhenusingtextwidth HowcanIremovealluvlayersbutthelastoneandrenameitonmultipleobjects? Howdoyoucomeupwithsuperpowersthathaven'tbeenoverused? HowdoItellmyDMthatdicedon’tneedtodictatethestory? Whydoest-statisticforarbitraryparameterfollowtdistribution? Whyaresomeembeddedregisterspasswordprotected? Howdoyoukeepanantagonistasmorallygreyevenaftertheycauseseriousharm/conflict? HowcanItellmybossthatmyproductivityislowduetoaconflictwithacoworker,withoutblamingthecoworker? Nonlinearoptimizationwithconstraintinvolvinglongproductofoptimizationvariables WhichmetrictousetoevaluateQuantileRegression? Howaretheprojectionsofthisplotmade? HowcanIrecoverthelosttimeinmycareer? Howarelawsrequiringgunbuyerstobe21+constitutional? WhomtocontactforaninevitableSchengenoverstaybecauseofmedicalreasons? morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. lang-vb Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings