Printing Macro Commands

BJTred

New Member
Joined
May 30, 2018
Messages
8
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this:

Code:
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:

Code:
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

New Member
Joined
May 30, 2018
Messages
8
Haven't had a chance to try this yet, but thanks, and I'll provide the results, hopefully today.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Do not worry, let me know if you have any doubts
 

BJTred

New Member
Joined
May 30, 2018
Messages
8
Dante, Thank you so much, your additions worked very nicely! Thanks again!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top