Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing schedule with auto increment workday date
Hi,
I'm trying to set up what I think should be a macro that runs on a print event. I have an office schedule that I print out for others to fill in. I'd like the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in that cell and automatically increase by 1 day for every page printed out, excluding Saturday and Sunday. Is this possible? I've been trying to figure out a way and so far have failed. Does anyone have any hints, suggestions, or tips that'll get me going in the right direction? Much appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing schedule with auto increment workday date
The bad news is that anything that relies on intercepting the
workbook_beforeprint will react to a file|print preview, too. I think I'd give the user a dedicated macro that would add one (or 3) to the date and then print that: Option Explicit Sub testme() With Worksheets("sheet1") With .Range("C1") If IsNumeric(.Value2) Then If Weekday(.Value) = vbFriday Then .Value = .Value + 3 Else .Value = .Value + 1 End If .Parent.PrintOut preview:=True End If End With End With End Sub I used preview:=true for testing purposes. Jenn P. wrote: Hi, I'm trying to set up what I think should be a macro that runs on a print event. I have an office schedule that I print out for others to fill in. I'd like the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in that cell and automatically increase by 1 day for every page printed out, excluding Saturday and Sunday. Is this possible? I've been trying to figure out a way and so far have failed. Does anyone have any hints, suggestions, or tips that'll get me going in the right direction? Much appreciated! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing schedule with auto increment workday date
Thank you ever so much, Dave! I appreciate your help. And, I don't mind the
print preview. (It's helpful.) I know I will be asked this next question, and that is, is it possible to print a range, say from January 2007 to February 2007, at once, instead of day by day? Again, much appreciated. "Dave Peterson" wrote: The bad news is that anything that relies on intercepting the workbook_beforeprint will react to a file|print preview, too. I think I'd give the user a dedicated macro that would add one (or 3) to the date and then print that: Option Explicit Sub testme() With Worksheets("sheet1") With .Range("C1") If IsNumeric(.Value2) Then If Weekday(.Value) = vbFriday Then .Value = .Value + 3 Else .Value = .Value + 1 End If .Parent.PrintOut preview:=True End If End With End With End Sub I used preview:=true for testing purposes. Jenn P. wrote: Hi, I'm trying to set up what I think should be a macro that runs on a print event. I have an office schedule that I print out for others to fill in. I'd like the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in that cell and automatically increase by 1 day for every page printed out, excluding Saturday and Sunday. Is this possible? I've been trying to figure out a way and so far have failed. Does anyone have any hints, suggestions, or tips that'll get me going in the right direction? Much appreciated! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing schedule with auto increment workday date
January 1, 2007 to February 28, 2007?
Option Explicit Sub testme() Dim dCtr As Long With Worksheets("sheet1") For dCtr = DateSerial(2007, 1, 1) To DateSerial(2007, 2, 28) If Weekday(dCtr) = vbSaturday _ Or Weekday(dCtr) = vbSunday Then 'do nothing Else .Range("C1").Value = dCtr 'you may not need this next line .Range("C1").EntireColumn.AutoFit .PrintOut preview:=True End If Next dCtr End With End Sub Jenn P. wrote: Thank you ever so much, Dave! I appreciate your help. And, I don't mind the print preview. (It's helpful.) I know I will be asked this next question, and that is, is it possible to print a range, say from January 2007 to February 2007, at once, instead of day by day? Again, much appreciated. "Dave Peterson" wrote: The bad news is that anything that relies on intercepting the workbook_beforeprint will react to a file|print preview, too. I think I'd give the user a dedicated macro that would add one (or 3) to the date and then print that: Option Explicit Sub testme() With Worksheets("sheet1") With .Range("C1") If IsNumeric(.Value2) Then If Weekday(.Value) = vbFriday Then .Value = .Value + 3 Else .Value = .Value + 1 End If .Parent.PrintOut preview:=True End If End With End With End Sub I used preview:=true for testing purposes. Jenn P. wrote: Hi, I'm trying to set up what I think should be a macro that runs on a print event. I have an office schedule that I print out for others to fill in. I'd like the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in that cell and automatically increase by 1 day for every page printed out, excluding Saturday and Sunday. Is this possible? I've been trying to figure out a way and so far have failed. Does anyone have any hints, suggestions, or tips that'll get me going in the right direction? Much appreciated! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing schedule with auto increment workday date
Thank you Dave! You've been a tremendous help. Thank you so much! :)
Cheers! Jenn :) "Dave Peterson" wrote: January 1, 2007 to February 28, 2007? Option Explicit Sub testme() Dim dCtr As Long With Worksheets("sheet1") For dCtr = DateSerial(2007, 1, 1) To DateSerial(2007, 2, 28) If Weekday(dCtr) = vbSaturday _ Or Weekday(dCtr) = vbSunday Then 'do nothing Else .Range("C1").Value = dCtr 'you may not need this next line .Range("C1").EntireColumn.AutoFit .PrintOut preview:=True End If Next dCtr End With End Sub Jenn P. wrote: Thank you ever so much, Dave! I appreciate your help. And, I don't mind the print preview. (It's helpful.) I know I will be asked this next question, and that is, is it possible to print a range, say from January 2007 to February 2007, at once, instead of day by day? Again, much appreciated. "Dave Peterson" wrote: The bad news is that anything that relies on intercepting the workbook_beforeprint will react to a file|print preview, too. I think I'd give the user a dedicated macro that would add one (or 3) to the date and then print that: Option Explicit Sub testme() With Worksheets("sheet1") With .Range("C1") If IsNumeric(.Value2) Then If Weekday(.Value) = vbFriday Then .Value = .Value + 3 Else .Value = .Value + 1 End If .Parent.PrintOut preview:=True End If End With End With End Sub I used preview:=true for testing purposes. Jenn P. wrote: Hi, I'm trying to set up what I think should be a macro that runs on a print event. I have an office schedule that I print out for others to fill in. I'd like the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in that cell and automatically increase by 1 day for every page printed out, excluding Saturday and Sunday. Is this possible? I've been trying to figure out a way and so far have failed. Does anyone have any hints, suggestions, or tips that'll get me going in the right direction? Much appreciated! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing schedule with auto increment workday date
Glad it worked for you.
Jenn P. wrote: Thank you Dave! You've been a tremendous help. Thank you so much! :) Cheers! Jenn :) "Dave Peterson" wrote: January 1, 2007 to February 28, 2007? Option Explicit Sub testme() Dim dCtr As Long With Worksheets("sheet1") For dCtr = DateSerial(2007, 1, 1) To DateSerial(2007, 2, 28) If Weekday(dCtr) = vbSaturday _ Or Weekday(dCtr) = vbSunday Then 'do nothing Else .Range("C1").Value = dCtr 'you may not need this next line .Range("C1").EntireColumn.AutoFit .PrintOut preview:=True End If Next dCtr End With End Sub Jenn P. wrote: Thank you ever so much, Dave! I appreciate your help. And, I don't mind the print preview. (It's helpful.) I know I will be asked this next question, and that is, is it possible to print a range, say from January 2007 to February 2007, at once, instead of day by day? Again, much appreciated. "Dave Peterson" wrote: The bad news is that anything that relies on intercepting the workbook_beforeprint will react to a file|print preview, too. I think I'd give the user a dedicated macro that would add one (or 3) to the date and then print that: Option Explicit Sub testme() With Worksheets("sheet1") With .Range("C1") If IsNumeric(.Value2) Then If Weekday(.Value) = vbFriday Then .Value = .Value + 3 Else .Value = .Value + 1 End If .Parent.PrintOut preview:=True End If End With End With End Sub I used preview:=true for testing purposes. Jenn P. wrote: Hi, I'm trying to set up what I think should be a macro that runs on a print event. I have an office schedule that I print out for others to fill in. I'd like the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in that cell and automatically increase by 1 day for every page printed out, excluding Saturday and Sunday. Is this possible? I've been trying to figure out a way and so far have failed. Does anyone have any hints, suggestions, or tips that'll get me going in the right direction? Much appreciated! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Should be easy...Auto Date Fill | Excel Discussion (Misc queries) | |||
Auto format quarter start date | Excel Discussion (Misc queries) | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
auto dating after entering the first date | Excel Worksheet Functions | |||
assign auto number and auto date | Excel Discussion (Misc queries) |