Print multiple copies of the same sheet, but replace one cell with the data from a list (range) from another sheet

Question

I am trying to print a few months worth of time sheets. So print 20 copies of the same sheet, and change the date on one cell (cell "C1" on "Timesheets" sheet) using a list of fortnightly dates on the "Pay Periods" sheet.

Have tried multiple methods but can't get close for varying reasons...

Would be interested to learn why am getting errors or stuck on each method I have tried below.

Sub PrintAllDates()
    Dim printDate As Date
    Dim startDate As Date
    Dim endDate As Date

    startDate = Worksheets("Pay Periods").Range("A2")
    endDate = Worksheets("Pay Periods").Range("A10")
    
    For printDate = startDate To endDate
        Sheets("Timesheet").Range("C1") = printDate
        Sheets("Timesheet").PrintOut
Next

This works but I can't figure out how to get it to use the list. It prints out 9 consecutive days instead, whereas my list is 9 consecutive "fortnights".

Sub PrintCopies()
    Dim i As Integer
    Dim VList As Variant
    
    VList = Sheets("Pay Periods").Range("H2:H3").Value
      
    For i = LBound(VList) To UBound(VList)
        Range("C1") = VList(i)
        ActiveSheet.PrintOut
    Next

With the above, I get runtime error 9 "Subscript out of range" on Range("C1") = VList(i)

Sub PrintCopies()
    Dim i As Date
    Dim VList As Variant

    VList = Array(Worksheets("Pay Periods").Range("A2:A10"))
    For i = LBound(VList) To UBound(VList)
        Sheets("Timesheet").Range("C1") = VList(i)
        Sheets("Timesheet").PrintOut
    Next

This also works, but only 1 page gets printed out. Date also gets converted to "13 Jan 1900".

Answer 1

The first code does not work because it is not considering the whole range of dates; instead it takes only the value inside the first and last cell, treating them as dates. The code basically takes those dates and covers each day between them. It does not even akwnoledge the others cells between A2 and A10. This one should work:

Sub PrintAllDates()
    
    'Declaring variables.
    Dim RngDate As Range
    Dim RngDates As Range
    Dim RngTarget As Range
    
    'Setting variables.
    Set RngDates = Sheets("Pay Periods").Range("A2:A10")
    Set RngTarget = Sheets("Timesheet").Range("C1")
    
    'Covering each cell in RngDates.
    For Each RngDate In RngDates
        
        'Changing RngTarget.
        RngTarget = RngDate.Value
        
        'Printing RngTarget's sheet.
        RngTarget.Parent.PrintOut
    
    Next
    
End Sub

I've also added a feature to check if the given value is a date in this version:

Sub PrintAllDates()
    
    'Declaring variables.
    Dim RngDate As Range
    Dim RngDates As Range
    Dim RngTarget As Range
    
    'Setting variables.
    Set RngDates = Sheets("Pay Periods").Range("A2:A10")
    Set RngTarget = Sheets("Timesheet").Range("C1")
    
    'Covering each cell in RngDates.
    For Each RngDate In RngDates
        
        'Checking if RngDate does not contain a date value.
        If Not VBA.Information.IsDate(RngDate.Value) Then
            
            'Asking what to do in case RngDate does not contain a date value.
            Select Case MsgBox("Range " & RngDate.Address(False, False) & " in sheet " & RngDate.Parent.Name & " contains the value """ & RngDate.Value & """, which is a non-date value." & vbCrLf & _
                               vbCrLf & _
                               vbCrLf & _
                               "Do you wish to use it and print anyway?" & vbCrLf & _
                               vbCrLf & _
                               "Press ""Yes"" to print it anyway." & vbCrLf & _
                               vbCrLf & _
                               "Press ""No"" to not print it and proceed to the next value." & vbCrLf & _
                               vbCrLf & _
                               "Press ""Cancel"" to stop the macro and print no more.", _
                               vbYesNoCancel, _
                               "Non-date value detected" _
                              )
                'If "Cancel" is pressed, the macro is terminated.
                Case Is = 2
                    Exit Sub
                'If "Yes" is pressed, the macro goes on.
                Case Is = 6
                    
                'If "No" is pressed, the macro goes to NextRngDate
                Case Is = 7
                    GoTo NextRngDate
            End Select
        End If
        
        'Changing RngTarget.
        RngTarget = RngDate.Value
        
        'Printing RngTarget's sheet.
        RngTarget.Parent.PrintOut
                
'Checkpoint.
NextRngDate:
    
    Next
    
End Sub

Answer 2

Your code can be something like this:

Sub PrintAllDates()
Dim listRange As Range ' Your range A2:A10 in "Pay Periods" sheet '
Dim oCurrentCell As Range   ' Single cell from this range '
Dim printedSheet As Worksheet   ' Target sheet - "Timesheet" '
Dim oTargetCell As Range    ' C1 - target cell (to set next date from list) '

    Set listRange = Worksheets("Pay Periods").Range("A2:A10")
    Set printedSheet = Worksheets("Timesheet")
    Set oTargetCell = printedSheet.Range("C1")
    
    For Each oCurrentCell In listRange.Cells
        oTargetCell = oCurrentCell
Rem If some cells in "Timesheet" has formulas which reffered to C1,
Rem  we need recalc it before printing
        printedSheet.Calculate
        printedSheet.PrintOut
    Next oCurrentCell
End Sub