For Loop Weekdays into a cell

[JohnZ1156]

I have a form where the data is totally dependent on the date in a specific cell. The sheet is "Consolidation" and the cell is "H1".
I'm trying to write a "For Loop" macro that will enter an inputted date into H1, then pause for a keystroke and then enter the previous weekday date.
Obviously, I would like it to avoid Saturday's and Sunday's.
If possible, I think I might like it to print out the form before changing to the next date.
This is WAY above my capabilities.
Thank you.

 

[Herakles]

I have a form where the data is totally dependent on the date in a specific cell. The sheet is "Consolidation" and the cell is "H1".
I'm trying to write a "For Loop" macro that will enter an inputted date into H1, then pause for a keystroke and then enter the previous weekday date.
Obviously, I would like it to avoid Saturday's and Sunday's.
If possible, I think I might like it to print out the form before changing to the next date.
This is WAY above my capabilities.
Thank you.

Try this.

Public Sub subPrintForm()
Dim string_Date As String
Dim WsConsolidation As Worksheet
Dim dteDate As Date
Dim strMsg As String
Dim strOrd As String
Dim varAns As Variant
Dim i As Integer

    ActiveWorkbook.Save

    Set WsConsolidation = Worksheets("Consolidation")
    
    string_Date = InputBox("Insert Date in format dd/mm/yyyy", _
        "Date Inputbox", Format(Now(), "dd/mm/yyyy"))
        
    If IsDate(string_Date) Then
    
        dteDate = CDate(string_Date)
        
        With WsConsolidation.Range("H1")
            .Value = dteDate
            .NumberFormat = "dd/mm/yyyy"
        End With
                
    Else
        MsgBox "Date Format is Invalid or you cancelled.", vbOKOnly, "Whoops!"
        Exit Sub
    End If

    Do While True
        
        strOrd = fncOrdinalIndicator(Day(dteDate))
                
        strMsg = "Print form for date " & Format(dteDate, "DDDD") & " " & strOrd & " " & Format(dteDate, "MMMM YYYY") & "?" & vbCrLf & _
                            "Select 'No' to not print this form." & vbCrLf & _
                            "Select 'Cancel' to discontinue printing."
                            
        varAns = MsgBox(strMsg, vbYesNoCancel, "Continue")
        
        Select Case varAns
        
            Case vbYes:
            
                ' Print the form here.
                
                WsConsolidation.UsedRange.PrintOut
                
                i = i + 1
            
            Case vbNo:
        
                ' No action needed.
        
            Case vbCancel:
            
                Exit Do
        
        End Select
       
        dteDate = DateSerial(Year(dteDate), Month(dteDate), Day(dteDate) - 1)
        
        With WsConsolidation.Range("H1")
            .Value = dteDate
        End With
        
    Loop
    
    MsgBox "Printing of " & i & " forms complete.", vbOKOnly, "Confirmation."

End Sub

Private Function fncOrdinalIndicator(ByVal Number As Long) As String
 
    Select Case Number Mod 100
        Case 11 To 13
            fncOrdinalIndicator = "th"
        Case Else
            Select Case Number Mod 10
                Case 1
                    fncOrdinalIndicator = "st"
                Case 2
                    fncOrdinalIndicator = "nd"
                Case 3
                    fncOrdinalIndicator = "rd"
                Case Else
                    fncOrdinalIndicator = "th"
            End Select
    End Select
     
    fncOrdinalIndicator = Number & fncOrdinalIndicator
 
End Function

 

[JohnZ1156]

Thanks for your help, but, something went wrong.
It accepted the date and placed the date into H1, but then when I clicked on YES to print the form, it kept printing just the form header over and over again, on separate pages. I had to turn off my printer to stop it.

 

[Herakles]

Thanks for your help, but, something went wrong.
It accepted the date and placed the date into H1, but then when I clicked on YES to print the form, it kept printing just the form header over and over again, on separate pages. I had to turn off my printer to stop it.

Record a macro to print the form and paste the code into my code to replace this line.

WsConsolidation.UsedRange.PrintOut

 

[JohnZ1156]

Thank you for your help.
I made a couple of modifications.