Printing Macro Commands

[BJTred]

Greetings,

A while back I received some help here. The macro is still working great! I was wondering though how to get it print a certain way. Here's what I have: A workbook with 6 different sheets in it. Each sheet has numbered rows with peoples names (Sheet 1 has 1-20, Sheet 2 has 21-45, etc, on up to sheet 6). I print these sheets out for ones who sign out equipment we have. Each sheet has a date. When printing before, I would have to print the workbook, manually change the date on each sheet, then print the next days, until I printed out a months worth. The macro made here for me, when run, would ask for a start date, how many copies, then would print them out changing the date for each set. This works great except for one thing. When it prints, it will print out each set (sheets 1-6), changes the date, then prints the next set, till I get the full month. I am trying to have it print one Sheet (Sheet1, Sheet 2, etc) at a time, changing the date each time. This is because I put one sheet on it's own clipboard. So on clipboard one I'd have Sheet1 (numbers 1-20) starting on say 4/1/2019 and the last Sheet on clipboard would be Sheet 1 (numbers 1-20) with the date 4/30/2019. Then it should go on to print sheet2 the same way, then sheet3, and so fourth. What I have to do now is manually separate them. I have tried changing the "Collate" line in the macro, from False, to True, but neither setting seems to make it print the way I'd like. Could another input box maybe be made to select a single (for example, "Which sheet do you want to print?", then I could input the sheet name if I only want one particular sheet, or "ALL" if I want to print them all)? Or some other command to accomplish something similar. I was just a the point of just making each sheet into it's own workbook, but I thought I'd see if the bright minds here could come up with a different solution. Thanks in advance. This is the macro I have now:

Public Sub Print_Workbook_Multiple_Copies()
Dim startDate As String, numCopies As String
Dim ws As Worksheet, n As Long

startDate = InputBox("Start date")
If startDate = "" Then Exit Sub
numCopies = InputBox("Number of copies")
If numCopies = "" Then Exit Sub

For n = 1 To numCopies
For Each ws In ThisWorkbook.Worksheets
ws.Range("E1").Value = CDate(startDate) + n - 1
Next
ThisWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next

End Sub

 

[DanteAmor]

Try this:

Public Sub Print_Workbook_Multiple_Copies()
    Dim startDate As String, numCopies As String
    Dim ws As Worksheet, n As Long
    
    startDate = InputBox("Start date")
    If startDate = "" Then Exit Sub
    numCopies = InputBox("Number of copies")
    If numCopies = "" Then Exit Sub
    
    For Each ws In ThisWorkbook.Worksheets
        For n = 1 To numCopies
            ws.Range("E1").Value = CDate(startDate) + n - 1
            ws.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Next
    Next


End Sub

To print a sheet use:

Public Sub Print_sheet()
    Dim startDate As String, numCopies As String, wSheet As String
    Dim ws As Worksheet, n As Long
    
    startDate = InputBox("Start date")
    If startDate = "" Then Exit Sub
    numCopies = InputBox("Number of copies")
    If numCopies = "" Then Exit Sub
    wSheet = InputBox("Sheet name")
    If wSheet = "" Then Exit Sub
    
    
    Set ws = Sheets(wSheet)
    For n = 1 To numCopies
        ws.Range("E1").Value = CDate(startDate) + n - 1
        ws.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Next


End Sub

 

[BJTred]

Haven't had a chance to try this yet, but thanks, and I'll provide the results, hopefully today.

 

[DanteAmor]

Do not worry, let me know if you have any doubts

 

[BJTred]

Dante, Thank you so much, your additions worked very nicely! Thanks again!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.