Forum Discussion
Macro to print double sided
az123330 The problem is that your macro sends out a new printcommand for each date, which the printer then immediately processes. If the sheet would be larger so there are two pages to print, I bet those would be printed in duplex. Here is a macro that appears to do the trick. It creates temporary copies of the worksheet, enters the date into them and then prints all of them in one go and subsequently deletes the copies:
Sub PrintAllDates()
Dim vSheets() As Variant
Dim printDate As Date
Dim startDate As Date
Dim endDate As Date
Dim lShtCt As Long
ReDim vSheets(1 To 1)
Application.Dialogs(xlDialogPrinterSetup).Show
Application.ScreenUpdating = False
startDate = "18/06/19"
endDate = "19/06/19"
For printDate = startDate To endDate
ActiveSheet.Range("C1") = printDate
lShtCt = lShtCt + 1
ReDim Preserve vSheets(1 To lShtCt)
vSheets(lShtCt) = ActiveSheet.Name
ActiveSheet.Copy ActiveSheet
Next
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets(vSheets).Select
Application.ScreenUpdating = False
ActiveWindow.SelectedSheets.PrintOut
'New delete the temporary sheets
' Make a list of them by removing the first sheet from the list (since that is our original)
For lShtCt = LBound(vSheets) To UBound(vSheets) - 1
vSheets(lShtCt) = vSheets(lShtCt + 1)
Next
ReDim Preserve vSheets(1 To lShtCt - 1)
'Now delete
Sheets(vSheets).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub
Thats fantastic! I really need to learn how to code on this. Works exactly as intended.
A bit of an out the box question but do you know if I can somehow have it so it skips weekends throughout the year? The register is only meant for Mon-Fri and since its double sided I can't just get rid of the weekends because they end up printing on the same page as the weekdays.
Thanks again
az123330 You can omit weekends like so:
Sub PrintAllDates() Dim vSheets() As Variant Dim printDate As Date Dim startDate As Date Dim endDate As Date Dim lShtCt As Long ReDim vSheets(1 To 1) Application.Dialogs(xlDialogPrinterSetup).Show Application.ScreenUpdating = False startDate = "15/06/19" endDate = "19/06/19" For printDate = startDate To endDate If Weekday(printDate, vbMonday) < 6 Then ActiveSheet.Range("C1") = printDate lShtCt = lShtCt + 1 ReDim Preserve vSheets(1 To lShtCt) vSheets(lShtCt) = ActiveSheet.Name ActiveSheet.Copy ActiveSheet End If Next Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Sheets(vSheets).Select Application.ScreenUpdating = False ActiveWindow.SelectedSheets.PrintOut 'New delete the temporary sheets ' Make a list of them by removing the first sheet from the list (since that is our original) For lShtCt = LBound(vSheets) To UBound(vSheets) - 1 vSheets(lShtCt) = vSheets(lShtCt + 1) Next ReDim Preserve vSheets(1 To lShtCt - 1) 'Now delete Sheets(vSheets).Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True End SubThanks Jan, that's exactly it. Trying to figure out what the code means you added. Would you mind explaining it?
I am going to try and break down all the code to get an understanding on whats being done line by line. Would you say its difficult to learn or once you get into it starts making more sense?
az123330 Here is a slightly adjusted version of the routine (less steps) including comments.
Option Explicit Sub PrintAllDates() Dim vSheets() As Variant 'Holds a list of the sheets to print Dim printDate As Date Dim startDate As Date Dim endDate As Date Dim lShtCt As Long 'remembers how many sheets were added 'Tell VBA how many items to expect, it'll be at least one. ReDim vSheets(1 To 1) Application.Dialogs(xlDialogPrinterSetup).Show Application.ScreenUpdating = False startDate = "15/06/19" endDate = "19/06/19" For printDate = startDate To endDate If Weekday(printDate, vbMonday) < 6 Then ActiveSheet.Copy ActiveSheet ActiveSheet.Range("C1") = printDate lShtCt = lShtCt + 1 'Match the size of the vSHeets list to the number of sheet names added ReDim Preserve vSheets(1 To lShtCt) 'Store the new sheet name vSheets(lShtCt) = ActiveSheet.Name End If Next 'Now select the newly inserted sheets Sheets(vSheets).Select Application.ScreenUpdating = False 'Print them ActiveWindow.SelectedSheets.PrintOut 'Select them again to make sure they're still selected so we can delete them Sheets(vSheets).Select 'SUppress the "Are you sure you want to delete" message: Application.DisplayAlerts = False 'Now delete the temporary sheets ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True End Sub