Macro to print double sided - Microsoft Tech Community

The issue I am having is the sheets will not print duplex no matter what I do. ... were added 'Tell VBA how many items to expect, it'll be at least one. Microsoft TechCommunity HomeCommunityHubsCommunityHubsCommunityHubsHomeProductsSpecialTopicsVideoHubCloseMostActiveHubsMicrosoftTeamsMicrosoftExcelWindowsSecurity,ComplianceandIdentityOffice365SharePointWindowsServerAzureExchangeMicrosoft365MicrosoftEdgeInsider.NETSharingbestpracticesforbuildinganyappwith.NET.MicrosoftFastTrackBestpracticesandthelatestnewsonMicrosoftFastTrackMicrosoftVivaTheemployeeexperienceplatformtohelppeoplethriveatworkMostActiveHubsITOpsTalkCoreInfrastructureandSecurityMicrosoftLearnEducationSectorMicrosoft365PnPAIandMachineLearningMicrosoftMechanicsHealthcareandLifeSciencesSmallandMediumBusinessPublicSectorInternetofThings(IoT)AzurePartnerCommunityExpandyourAzurepartner-to-partnernetworkMicrosoftTechTalksBringingITProstogetherthroughIn-Person&VirtualeventsMVPAwardProgramFindoutmoreabouttheMicrosoftMVPAwardProgram.VideoHubAzureExchangeMicrosoft365Microsoft365BusinessMicrosoft365EnterpriseMicrosoftEdgeMicrosoftOutlookMicrosoftTeamsSecuritySharePointWindowsBrowseAllCommunityHubsBlogsBlogsEventsEventsEventsHomeMicrosoftIgniteMicrosoftBuildCommunityEventsMicrosoftLearnMicrosoftLearnHomeCommunityBlogAzureDynamics365Microsoft365Security,Compliance&IdentityPowerPlatformGithubTeams.NETLoungeLounge861KMembers5,326Online246KDiscussions Search cancel Turnonsuggestions Auto-suggesthelpsyouquicklynarrowdownyoursearchresultsbysuggestingpossiblematchesasyoutype. Showingresultsfor  Show  only  | Searchinsteadfor  Didyoumean:  SignIn SignIn cancel Turnonsuggestions Auto-suggesthelpsyouquicklynarrowdownyoursearchresultsbysuggestingpossiblematchesasyoutype. Showingresultsfor  Show  only  | Searchinsteadfor  Didyoumean:  Home Home MicrosoftExcel Excel Macrotoprintdoublesided Macrotoprintdoublesided %3CLINGO-SUB%20id%3D%22lingo-sub-699032%22%20slang%3D%22en-US%22%3EMacro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699032%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20the%20attached%20spreadsheet%20and%20inserted%20the%20basic%20macro%20where%20it%20will%20print%20multiple%20sheets%20from%20and%20to%20the%20date%20range%20I%20specify%20in%20the%20macro.%20This%20is%20to%20act%20as%20a%20register%20and%20saves%20times%20copying%20and%20pasting%20the%20date%20each%20and%20every%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I%20am%20having%20is%20the%20sheets%20will%20not%20print%20duplex%20no%20matter%20what%20I%20do.%20I%20have%20tried%20changing%20the%20default%20printer%20and%20even%20put%20code%20in%20to%20select%20the%20printer%20before%20it%20prints%20the%20sheets.%20In%20setup%20duplex%20is%20selected%20but%20still%20comes%20out%20as%20single%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20an%20idea%20whats%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20alternative%20is%20to%20change%20the%20macro%20so%20it%20does%20the%20same%20thing%20but%20instead%20prints%20all%20sheets%20into%20one%20PDF%20file.%20From%20there%20I%20can%20print%20however%20I%20do%20not%20know%20how%20to%20write%20this%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-699032%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699176%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F275880%22%20target%3D%22_blank%22%3E%40az123330%3C%2FA%3E%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EThe%20problem%20is%20that%20your%20macro%20sends%20out%20a%20new%20printcommand%20for%20each%20date%2C%20which%20the%20printer%20then%20immediately%20processes.%20If%20the%20sheet%20would%20be%20larger%20so%20there%20are%20two%20pages%20to%20print%2C%20I%20bet%20those%20would%20be%20printed%20in%20duplex.%20Here%20is%20a%20macro%20that%20appears%20to%20do%20the%20trick.%20It%20creates%20temporary%20copies%20of%20the%20worksheet%2C%20enters%20the%20date%20into%20them%20and%20then%20prints%20all%20of%20them%20in%20one%20go%20and%20subsequently%20deletes%20the%20copies%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CPRE%3ESub%20PrintAllDates()%0A%20%20%20%20Dim%20vSheets()%20As%20Variant%0A%20%20%20%20Dim%20printDate%20As%20Date%0A%20%20%20%20Dim%20startDate%20As%20Date%0A%20%20%20%20Dim%20endDate%20As%20Date%0A%20%20%20%20Dim%20lShtCt%20As%20Long%0A%20%20%20%20ReDim%20vSheets(1%20To%201)%0A%20%20%20%20Application.Dialogs(xlDialogPrinterSetup).Show%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20startDate%20%3D%20%2218%2F06%2F19%22%0A%20%20%20%20endDate%20%3D%20%2219%2F06%2F19%22%0A%20%20%20%20For%20printDate%20%3D%20startDate%20To%20endDate%0A%20%20%20%20%20%20%20%20ActiveSheet.Range(%22C1%22)%20%3D%20printDate%0A%20%20%20%20%20%20%20%20lShtCt%20%3D%20lShtCt%20%2B%201%0A%20%20%20%20%20%20%20%20ReDim%20Preserve%20vSheets(1%20To%20lShtCt)%0A%20%20%20%20%20%20%20%20vSheets(lShtCt)%20%3D%20ActiveSheet.Name%0A%20%20%20%20%20%20%20%20ActiveSheet.Copy%20ActiveSheet%0A%20%20%20%20Next%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20ActiveSheet.Delete%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20Sheets(vSheets).Select%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20ActiveWindow.SelectedSheets.PrintOut%0A%20%20%20%20'New%20delete%20the%20temporary%20sheets%0A%20%20%20%20'%20Make%20a%20list%20of%20them%20by%20removing%20the%20first%20sheet%20from%20the%20list%20(since%20that%20is%20our%20original)%0A%20%20%20%20For%20lShtCt%20%3D%20LBound(vSheets)%20To%20UBound(vSheets)%20-%201%0A%20%20%20%20%20%20%20%20vSheets(lShtCt)%20%3D%20vSheets(lShtCt%20%2B%201)%0A%20%20%20%20Next%0A%20%20%20%20ReDim%20Preserve%20vSheets(1%20To%20lShtCt%20-%201)%0A%20%20%20%20'Now%20delete%0A%20%20%20%20Sheets(vSheets).Select%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20ActiveWindow.SelectedSheets.Delete%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0AEnd%20Sub%0A%3C%2FPRE%3E%0A%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699227%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThats%20fantastic!%20I%20really%20need%20to%20learn%20how%20to%20code%20on%20this.%20Works%20exactly%20as%20intended.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20bit%20of%20an%20out%20the%20box%20question%20but%20do%20you%20know%20if%20I%20can%20somehow%20have%20it%20so%20it%20skips%20weekends%20throughout%20the%20year%3F%20The%20register%20is%20only%20meant%20for%20Mon-Fri%20and%20since%20its%20double%20sided%20I%20can't%20just%20get%20rid%20of%20the%20weekends%20because%20they%20end%20up%20printing%20on%20the%20same%20page%20as%20the%20weekdays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699479%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699479%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F275880%22%20target%3D%22_blank%22%3E%40az123330%3C%2FA%3E%20You%20can%20omit%20weekends%20like%20so%3A%3C%2FP%3E%0A%3CPRE%3ESub%20PrintAllDates()%0A%20%20%20%20Dim%20vSheets()%20As%20Variant%0A%20%20%20%20Dim%20printDate%20As%20Date%0A%20%20%20%20Dim%20startDate%20As%20Date%0A%20%20%20%20Dim%20endDate%20As%20Date%0A%20%20%20%20Dim%20lShtCt%20As%20Long%0A%20%20%20%20ReDim%20vSheets(1%20To%201)%0A%20%20%20%20Application.Dialogs(xlDialogPrinterSetup).Show%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20startDate%20%3D%20%2215%2F06%2F19%22%0A%20%20%20%20endDate%20%3D%20%2219%2F06%2F19%22%0A%20%20%20%20For%20printDate%20%3D%20startDate%20To%20endDate%0A%20%20%20%20%20%20%20%20If%20Weekday(printDate%2C%20vbMonday)%20%26lt%3B%206%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Range(%22C1%22)%20%3D%20printDate%0A%20%20%20%20%20%20%20%20%20%20%20%20lShtCt%20%3D%20lShtCt%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20ReDim%20Preserve%20vSheets(1%20To%20lShtCt)%0A%20%20%20%20%20%20%20%20%20%20%20%20vSheets(lShtCt)%20%3D%20ActiveSheet.Name%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Copy%20ActiveSheet%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20ActiveSheet.Delete%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20Sheets(vSheets).Select%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20ActiveWindow.SelectedSheets.PrintOut%0A%20%20%20%20'New%20delete%20the%20temporary%20sheets%0A%20%20%20%20'%20Make%20a%20list%20of%20them%20by%20removing%20the%20first%20sheet%20from%20the%20list%20(since%20that%20is%20our%20original)%0A%20%20%20%20For%20lShtCt%20%3D%20LBound(vSheets)%20To%20UBound(vSheets)%20-%201%0A%20%20%20%20%20%20%20%20vSheets(lShtCt)%20%3D%20vSheets(lShtCt%20%2B%201)%0A%20%20%20%20Next%0A%20%20%20%20ReDim%20Preserve%20vSheets(1%20To%20lShtCt%20-%201)%0A%20%20%20%20'Now%20delete%0A%20%20%20%20Sheets(vSheets).Select%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20ActiveWindow.SelectedSheets.Delete%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0AEnd%20Sub%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699636%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Jan%2C%20that's%20exactly%20it.%20Trying%20to%20figure%20out%20what%20the%20code%20means%20you%20added.%20Would%20you%20mind%20explaining%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20going%20to%20try%20and%20break%20down%20all%20the%20code%20to%20get%20an%20understanding%20on%20whats%20being%20done%20line%20by%20line.%20Would%20you%20say%20its%20difficult%20to%20learn%20or%20once%20you%20get%20into%20it%20starts%20making%20more%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699764%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F275880%22%20target%3D%22_blank%22%3E%40az123330%3C%2FA%3E%20Here%20is%20a%20slightly%20adjusted%20version%20of%20the%20routine%20(less%20steps)%20including%20comments.%3C%2FP%3E%0A%3CPRE%3EOption%20Explicit%0A%0ASub%20PrintAllDates()%0A%20%20%20%20Dim%20vSheets()%20As%20Variant%20'Holds%20a%20list%20of%20the%20sheets%20to%20print%0A%20%20%20%20Dim%20printDate%20As%20Date%0A%20%20%20%20Dim%20startDate%20As%20Date%0A%20%20%20%20Dim%20endDate%20As%20Date%0A%20%20%20%20Dim%20lShtCt%20As%20Long%20'remembers%20how%20many%20sheets%20were%20added%0A%20%20%20%20'Tell%20VBA%20how%20many%20items%20to%20expect%2C%20it'll%20be%20at%20least%20one.%0A%20%20%20%20ReDim%20vSheets(1%20To%201)%0A%20%20%20%20Application.Dialogs(xlDialogPrinterSetup).Show%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20startDate%20%3D%20%2215%2F06%2F19%22%0A%20%20%20%20endDate%20%3D%20%2219%2F06%2F19%22%0A%20%20%20%20For%20printDate%20%3D%20startDate%20To%20endDate%0A%20%20%20%20%20%20%20%20If%20Weekday(printDate%2C%20vbMonday)%20%26lt%3B%206%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Copy%20ActiveSheet%0A%20%20%20%20%20%20%20%20%20%20%20%20ActiveSheet.Range(%22C1%22)%20%3D%20printDate%0A%20%20%20%20%20%20%20%20%20%20%20%20lShtCt%20%3D%20lShtCt%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20'Match%20the%20size%20of%20the%20vSHeets%20list%20to%20the%20number%20of%20sheet%20names%20added%0A%20%20%20%20%20%20%20%20%20%20%20%20ReDim%20Preserve%20vSheets(1%20To%20lShtCt)%0A%20%20%20%20%20%20%20%20%20%20%20%20'Store%20the%20new%20sheet%20name%0A%20%20%20%20%20%20%20%20%20%20%20%20vSheets(lShtCt)%20%3D%20ActiveSheet.Name%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20'Now%20select%20the%20newly%20inserted%20sheets%0A%20%20%20%20Sheets(vSheets).Select%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20'Print%20them%0A%20%20%20%20ActiveWindow.SelectedSheets.PrintOut%0A%20%20%20%20'Select%20them%20again%20to%20make%20sure%20they're%20still%20selected%20so%20we%20can%20delete%20them%0A%20%20%20%20Sheets(vSheets).Select%0A%20%20%20%20'SUppress%20the%20%22Are%20you%20sure%20you%20want%20to%20delete%22%20message%3A%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20'Now%20delete%20the%20temporary%20sheets%0A%20%20%20%20ActiveWindow.SelectedSheets.Delete%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0AEnd%20Sub%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699907%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThats%20very%20helpful%2C%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20other%20issue%20there%20seems%20to%20be%20is%20the%20sheets%20look%20to%20print%20from%20the%20end%20date%20to%20the%20first%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20only%20an%20issue%20because%20if%20the%20number%20of%20sheets%20is%20odd%20then%20its%20the%20first%20sheet%20which%20will%20print%20single%20rather%20than%20the%20end%20sheet.%20Only%20a%20minor%20thing%20but%20since%20its%20not%20mentioned%20in%20the%20code%20I%20am%20unsure%20why%20it%20behaves%20in%20this%20way%20rather%20than%20from%20start%20to%20finish%20like%20you'd%20expect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-700092%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20print%20double%20sided%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-700092%22%20slang%3D%22en-US%22%3E%3CP%3EOnly%20a%20very%20small%20change%20is%20needed%20to%20fix%20that.%20Find%20this%20line%20of%20code%3A%3CBR%20%2F%3E%3CBR%20%2F%3EActiveSheet.Copy%20ActiveSheet%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20change%20it%20to%3A%3CBR%20%2F%3E%3CBR%20%2F%3EActiveSheet.Copy%20after%3A%3DActiveSheet%3C%2FP%3E%3C%2FLINGO-BODY%3E DiscussionOptions SubscribetoRSSFeed MarkDiscussionasNew MarkDiscussionasRead PinthisDiscussionforCurrentUser Bookmark Subscribe PrinterFriendlyPage az123330 OccasionalContributor ‎Jun182019 01:13AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 01:13AM Macrotoprintdoublesided Hi, IhavecreatedtheattachedspreadsheetandinsertedthebasicmacrowhereitwillprintmultiplesheetsfromandtothedaterangeIspecifyinthemacro.Thisistoactasaregisterandsavestimescopyingandpastingthedateeachandeverytime. TheissueIamhavingisthesheetswillnotprintduplexnomatterwhatIdo.Ihavetriedchangingthedefaultprinterandevenputcodeintoselecttheprinterbeforeitprintsthesheets.Insetupduplexisselectedbutstillcomesoutassinglesheets. Doesanyonehaveanideawhatswrong? ThealternativeistochangethemacrosoitdoesthesamethingbutinsteadprintsallsheetsintoonePDFfile.FromthereIcanprinthoweverIdonotknowhowtowritethiscode? Thanksinadvance Labels: Labels: Developer Excel MacrosandVBA Previewfile 22KB 12KViews0Likes7Replies Reply AllDiscussions PreviousDiscussion NextDiscussion 7Replies JanKarelPieterse  repliedto  az123330 ‎Jun182019 02:00AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 02:00AM Re:Macrotoprintdoublesided @az123330 Theproblemisthatyourmacrosendsoutanewprintcommandforeachdate,whichtheprinterthenimmediatelyprocesses.Ifthesheetwouldbelargersotherearetwopagestoprint,Ibetthosewouldbeprintedinduplex.Hereisamacrothatappearstodothetrick.Itcreatestemporarycopiesoftheworksheet,entersthedateintothemandthenprintsalloftheminonegoandsubsequentlydeletesthecopies: SubPrintAllDates() DimvSheets()AsVariant DimprintDateAsDate DimstartDateAsDate DimendDateAsDate DimlShtCtAsLong ReDimvSheets(1To1) Application.Dialogs(xlDialogPrinterSetup).Show Application.ScreenUpdating=False startDate="18/06/19" endDate="19/06/19" ForprintDate=startDateToendDate ActiveSheet.Range("C1")=printDate lShtCt=lShtCt+1 ReDimPreservevSheets(1TolShtCt) vSheets(lShtCt)=ActiveSheet.Name ActiveSheet.CopyActiveSheet Next Application.DisplayAlerts=False ActiveSheet.Delete Application.DisplayAlerts=True Sheets(vSheets).Select Application.ScreenUpdating=False ActiveWindow.SelectedSheets.PrintOut 'Newdeletethetemporarysheets 'Makealistofthembyremovingthefirstsheetfromthelist(sincethatisouroriginal) ForlShtCt=LBound(vSheets)ToUBound(vSheets)-1 vSheets(lShtCt)=vSheets(lShtCt+1) Next ReDimPreservevSheets(1TolShtCt-1) 'Nowdelete Sheets(vSheets).Select Application.DisplayAlerts=False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts=True EndSub   0Likes Reply az123330  repliedto  JanKarelPieterse ‎Jun182019 02:45AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 02:45AM Re:Macrotoprintdoublesided @JanKarelPieterse  Thatsfantastic!Ireallyneedtolearnhowtocodeonthis.Worksexactlyasintended. AbitofanouttheboxquestionbutdoyouknowifIcansomehowhaveitsoitskipsweekendsthroughouttheyear?TheregisterisonlymeantforMon-FriandsinceitsdoublesidedIcan'tjustgetridoftheweekendsbecausetheyendupprintingonthesamepageastheweekdays. Thanksagain 0Likes Reply JanKarelPieterse  repliedto  az123330 ‎Jun182019 05:24AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 05:24AM Re:Macrotoprintdoublesided @az123330Youcanomitweekendslikeso: SubPrintAllDates() DimvSheets()AsVariant DimprintDateAsDate DimstartDateAsDate DimendDateAsDate DimlShtCtAsLong ReDimvSheets(1To1) Application.Dialogs(xlDialogPrinterSetup).Show Application.ScreenUpdating=False startDate="15/06/19" endDate="19/06/19" ForprintDate=startDateToendDate IfWeekday(printDate,vbMonday)<6Then ActiveSheet.Range("C1")=printDate lShtCt=lShtCt+1 ReDimPreservevSheets(1TolShtCt) vSheets(lShtCt)=ActiveSheet.Name ActiveSheet.CopyActiveSheet EndIf Next Application.DisplayAlerts=False ActiveSheet.Delete Application.DisplayAlerts=True Sheets(vSheets).Select Application.ScreenUpdating=False ActiveWindow.SelectedSheets.PrintOut 'Newdeletethetemporarysheets 'Makealistofthembyremovingthefirstsheetfromthelist(sincethatisouroriginal) ForlShtCt=LBound(vSheets)ToUBound(vSheets)-1 vSheets(lShtCt)=vSheets(lShtCt+1) Next ReDimPreservevSheets(1TolShtCt-1) 'Nowdelete Sheets(vSheets).Select Application.DisplayAlerts=False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts=True EndSub 0Likes Reply az123330  repliedto  JanKarelPieterse ‎Jun182019 06:23AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 06:23AM Re:Macrotoprintdoublesided @JanKarelPieterse  ThanksJan,that'sexactlyit.Tryingtofigureoutwhatthecodemeansyouadded.Wouldyoumindexplainingit? Iamgoingtotryandbreakdownallthecodetogetanunderstandingonwhatsbeingdonelinebyline.Wouldyousayitsdifficulttolearnoronceyougetintoitstartsmakingmoresense? 0Likes Reply JanKarelPieterse  repliedto  az123330 ‎Jun182019 07:05AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 07:05AM Re:Macrotoprintdoublesided @az123330Hereisaslightlyadjustedversionoftheroutine(lesssteps)includingcomments. OptionExplicit SubPrintAllDates() DimvSheets()AsVariant'Holdsalistofthesheetstoprint DimprintDateAsDate DimstartDateAsDate DimendDateAsDate DimlShtCtAsLong'remembershowmanysheetswereadded 'TellVBAhowmanyitemstoexpect,it'llbeatleastone. ReDimvSheets(1To1) Application.Dialogs(xlDialogPrinterSetup).Show Application.ScreenUpdating=False startDate="15/06/19" endDate="19/06/19" ForprintDate=startDateToendDate IfWeekday(printDate,vbMonday)<6Then ActiveSheet.CopyActiveSheet ActiveSheet.Range("C1")=printDate lShtCt=lShtCt+1 'MatchthesizeofthevSHeetslisttothenumberofsheetnamesadded ReDimPreservevSheets(1TolShtCt) 'Storethenewsheetname vSheets(lShtCt)=ActiveSheet.Name EndIf Next 'Nowselectthenewlyinsertedsheets Sheets(vSheets).Select Application.ScreenUpdating=False 'Printthem ActiveWindow.SelectedSheets.PrintOut 'Selectthemagaintomakesurethey'restillselectedsowecandeletethem Sheets(vSheets).Select 'SUppressthe"Areyousureyouwanttodelete"message: Application.DisplayAlerts=False 'Nowdeletethetemporarysheets ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts=True EndSub 0Likes Reply az123330  repliedto  JanKarelPieterse ‎Jun182019 07:46AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 07:46AM Re:Macrotoprintdoublesided @JanKarelPieterse Thatsveryhelpful,thanks. Theonlyotherissuethereseemstobeisthesheetslooktoprintfromtheenddatetothefirstdate. Thisisonlyanissuebecauseifthenumberofsheetsisoddthenitsthefirstsheetwhichwillprintsingleratherthantheendsheet.OnlyaminorthingbutsinceitsnotmentionedinthecodeIamunsurewhyitbehavesinthiswayratherthanfromstarttofinishlikeyou'dexpect. 0Likes Reply JanKarelPieterse  repliedto  az123330 ‎Jun182019 08:27AM -edited ‎Jun182019 08:27AM MarkasNew Bookmark Subscribe Mute SubscribetoRSSFeed Permalink Print EmailtoaFriend ReportInappropriateContent ‎Jun182019 08:27AM -edited ‎Jun182019 08:27AM Re:Macrotoprintdoublesided Onlyaverysmallchangeisneededtofixthat.Findthislineofcode:ActiveSheet.CopyActiveSheetandchangeitto:ActiveSheet.Copyafter:=ActiveSheet 0Likes Reply Share SharetoLinkedIn SharetoFacebook SharetoTwitter SharetoEmail Browse What'snew SurfaceProX SurfaceLaptop3 SurfacePro7 Windows10Apps Officeapps MicrosoftStore Accountprofile DownloadCenter MicrosoftStoresupport Returns Ordertracking Storelocations Buyonline,pickupinstore In-storeevents Education Microsoftineducation Officeforstudents Officeforschools Dealsforstudentsandparents MicrosoftAzureineducation Enterprise Azure AppSource Automotive Government Healthcare Manufacturing FinancialServices Retail Developer MicrosoftVisualStudio WindowDevCenter DeveloperNetwork TechNet Microsoftdeveloperprogram Channel9 OfficeDevCenter MicrosoftGarage Company Careers AboutMicrosoft CompanyNews PrivacyatMicrosoft Investors Diversityandinclusion Accessibility Security Sitemap ContactMicrosoft Privacy Managecookies Termsofuse Trademarks Safetyandeco Aboutourads © Microsoft