Jun 18 2019 01:13 AM
Hi,
I have created the attached spreadsheet and inserted the basic macro where it will print multiple sheets from and to the date range I specify in the macro. This is to act as a register and saves times copying and pasting the date each and every time.
The issue I am having is the sheets will not print duplex no matter what I do. I have tried changing the default printer and even put code in to select the printer before it prints the sheets. In setup duplex is selected but still comes out as single sheets.
Does anyone have an idea whats wrong?
The alternative is to change the macro so it does the same thing but instead prints all sheets into one PDF file. From there I can print however I do not know how to write this code?
Thanks in advance
Jun 18 2019 02:00 AM
@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
Jun 18 2019 02:45 AM
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
Jun 18 2019 05:24 AM
@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
Jun 18 2019 06:23 AM
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?
Jun 18 2019 07:05 AM
@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
Jun 18 2019 07:46 AM
Thats 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.
Jun 18 2019 08:27 AM - edited Jun 18 2019 08:27 AM
Only a very small change is needed to fix that. Find this line of code:
ActiveSheet.Copy ActiveSheet
and change it to:
ActiveSheet.Copy after:=ActiveSheet