Trying to increment by one day in a macro

[PhilB]

Hello,
Having all sorts of fun getting this one worked out, to date, n
success at all
I basically one to have a form to collect the start date and nr of day
required then print the worksheet with the dates incrementing from th
start date by one for the number of days required.
This is what I have tried:

Private Sub cmdPrint_Click()
'Dim Variables
Dim D As Integer
Sheets("LogSheet").Select
Range("Date") = ""
'Transfer Info from frmDriverLogInfo
Sheets("LogSheet").Select
Range("Date") = txtStartDate.Value
D = cboNrOfDays.Value
'Print Selected Number of Days
For I = 1 To D
Sheets("LogSheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Range("Date").Value = ActiveSheet.Range("Date").Value
1
Next I
End Sub

Any help would be most appreciated.
Many thanks, Phi

 

[Dave Peterson]

Your code worked for me--after I dimmed I--and if I put valid entries in each
choice.

I'm guessing that you were typing something in that excel couldn't see as a
date.

Option Explicit
Private Sub cmdPrint_Click()
'Dim Variables
Dim D As Long
Dim I As Long

With Sheets("LogSheet")
If IsDate(Me.txtStartDate.Value) Then
.Range("Date") = CDate(Me.txtStartDate.Value)
Else
MsgBox "Please enter a date!"
Exit Sub
End If

D = 0
If IsNumeric(Me.cboNrOfDays.Value) Then
D = CLng(Me.cboNrOfDays.Value)
End If
If D = 0 Then
MsgBox "Please enter the number of days"
Exit Sub
End If

'Print Selected Number of Days
For I = 1 To D
.PrintOut preview:=True, Copies:=1, Collate:=True
.Range("Date").Value = .Range("Date").Value + 1
Next I
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.cboNrOfDays
.Clear
For iCtr = 1 To 5
.AddItem iCtr
Next iCtr
End With
End Sub

(I added the preview:=true to save some trees while testing.)

 

[PhilB]

Dave,
This is what I ended up, borrowing the code you supplied an
transferring the 'date' range to another worksheet. I guessed that i
may have been an issue with formatting the date on the printed shee
that was causing the problem as I was trying to seperate d/mmm/yyy
into three seperate cells to prevent the ### problem in columns tha
were fitting the other information.

Private Sub cmdPrint_Click()
'Dim Variables
Dim D As Long
Dim I As Long

With Sheets("DataSheet")
If IsDate(Me.txtStartDate.Value) Then
Sheets("DataSheet").Range("date") = CDate(Me.txtStartDate.Value)
Else
MsgBox "Please enter a date!"
Exit Sub
End If

D = 0
If IsNumeric(Me.cboNrOfDays.Value) Then
D = CLng(Me.cboNrOfDays.Value)
End If
If D = 0 Then
MsgBox "Please enter the number of days"
Exit Sub
End If
'Unload Form
Unload frmDriverLogInfo
'Print Selected Number of Days
For I = 1 To D
'Change PrintOut to PrintOut preview = true for testing
Sheets("LogSheet").PrintOut Copies:=1, Collate:=True
Sheets("DataSheet").Range("date").Value
Sheets("DataSheet").Range("date").Value + 1
Next I
End With
Sheets("DataSheet").Range("date").Value
Sheets("DataSheet").Range("date").Value - 1
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit
End Sub


Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.cboNrOfDays
.Clear
For iCtr = 1 To 7
.AddItem iCtr
Next iCtr
End With
End Sub

I tacked the -1 day on the end to save it at the last day printed.
Many thanks for all your help, it certainly made the problem les
problematic.
Take care, Phi