Trying to increment by one day in a macro
 

Hello,
Having all sorts of fun getting this one worked out, to date, n
success at all :rolleyes:
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

--
Phil
-----------------------------------------------------------------------
PhilB's Profile: http://www.excelforum.com/member.php...fo&userid=1286
View this thread: http://www.excelforum.com/showthread.php?threadid=31897

Trying to increment by one day in a macro
 

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

Hello,
Having all sorts of fun getting this one worked out, to date, no
success at all :rolleyes:
I basically one to have a form to collect the start date and nr of days
required then print the worksheet with the dates incrementing from the
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, Phil

--
PhilB
------------------------------------------------------------------------
PhilB's Profile: http://www.excelforum.com/member.php...o&userid=12866
View this thread: http://www.excelforum.com/showthread...hreadid=318973

--

Dave Peterson

Trying to increment by one day in a macro
 

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

--
Phil
-----------------------------------------------------------------------
PhilB's Profile: http://www.excelforum.com/member.php...fo&userid=1286
View this thread: http://www.excelforum.com/showthread.php?threadid=31897

Trying to increment by one day in a macro
 

Getting a date from a Textbox or inputbox can be a problem.

You may want to consider adding a calendar control to your form. (It'll look
pretty neat, too!)

Ron de Bruin has some samples at:
http://www.rondebruin.nl/calendar.htm

Ron has a link (at the bottom of that URL) where you can get a free version of a
control. (It's not included in excel (but if you have it, you can use it
there.) (I think it's installed with Access.)



PhilB wrote:

Dave,
This is what I ended up, borrowing the code you supplied and
transferring the 'date' range to another worksheet. I guessed that it
may have been an issue with formatting the date on the printed sheet
that was causing the problem as I was trying to seperate d/mmm/yyyy
into three seperate cells to prevent the ### problem in columns that
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 less
problematic.
Take care, Phil

--
PhilB
------------------------------------------------------------------------
PhilB's Profile: http://www.excelforum.com/member.php...o&userid=12866
View this thread: http://www.excelforum.com/showthread...hreadid=318973

--

Dave Peterson