Advance date by one after each printed copy

[BJTred]

Is it possible to have Excel advance the date by one for each copy of the workbook I print? I have a workbook with names on it, used to sign out equipment. The workbook has 6 separate sheets in it. I have the date in one cell on each sheet (it's the same cell on each sheet). Currently I print one copy, change the date on all 6 sheets (using find and replace), then print, change the date on all 6 sheets, then print, and so on and so on. Is there a way to automate this, so that I can put in the start date, tell it I want 30 copies and it will change the date (on all 6 sheets) after it prints each copy? Hope that makes sense.Thanks.BJTred

 

[jproffer]

You can do it before you print, which is essentially the same thing after the first time . Don't know your sheet names or ranges but try something like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1
End Sub

By the way, that goes into the workbook code module.

 

[John_w]

That Workbook_BeforePrint code would require the user to manually print each copy.

Try this instead (code in a standard module and A2 in every sheet is the date cell).

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("A2").Value = CDate(startDate) + n - 1
        Next
        ThisWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Next
    
End Sub