Forum Discussion
Macro to print double sided
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 Sub
Thanks 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 SubThats very helpful, thanks.
The only other issue there seems to be is the sheets look to print from the end date to the first date.
This is only an issue because if the number of sheets is odd then its the first sheet which will print single rather than the end sheet. Only a minor thing but since its not mentioned in the code I am unsure why it behaves in this way rather than from start to finish like you'd expect.