Employee Absence Schedule Template

Thanks Julie.
I was successful.
Excellent.

"JulieD" wrote:

> Hi Jayant
>
> Here's some code that will generate this type of schedule for any year and
> any number of employees.
>
> 'start code
>
> Sub create_schedule()
> 'written by Julie Dall 2006
>
> Dim for_year As Long 'year to create schedule for
> Dim num_emp As Long 'number of employees to cater for
> Dim i, j, k, m As Long
>
> 'populate variables
> for_year = InputBox("Enter the year to create the schedule for",
> "Year?", Format(Now, "yyyy"))
> num_emp = InputBox("Enter the number of employees to cater for", "Number
> Employees", 10)
>
> Application.Workbooks.Add 'add a new workbook
>
> 'create schedule
> ActiveSheet.Range("A1").Select
>
> ActiveCell.Value = "Employee Absence Schedule for " & for_year
> ActiveCell.Font.Bold = True
> ActiveCell.Font.ColorIndex = 36
> ActiveCell.Interior.ColorIndex = 53
>
> For i = 1 To 12
> 'put months in
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value =
> Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value =
> "Employee Name"
> 'format
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> True
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> 0).Font.ColorIndex = 36
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> 0).Interior.ColorIndex = 53
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> True
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> 0).Interior.ColorIndex = 19
> For k = 1 To num_emp + 1
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> 0).Interior.ColorIndex = 19
> Next
>
> 'put day header in
> For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value =
> Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> j).Interior.ColorIndex = 19
> 'format
> For k = 0 To num_emp + 1
> If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> j).Value = "S" Then
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> j).Interior.ColorIndex = 19
> End If
> Next
> 'put days in
> For m = 1 To num_emp + 1
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m,
> j).Value = Format(DateSerial(for_year, i, j), "d")
> Next
> Next
> Next
>
> 'Fit columns
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.Columns.AutoFit
> Range("A1").Select
> Columns(1).HorizontalAlignment = xlJustify
>
> MsgBox "Finished"
>
> End Sub
>
> 'end code
>
> this code can be copied & pasted into the code module of a workbook (line
> breaks removed) and when run, it will create a new workbook for you with the
> schedule information.
>
> If you would prefer, email me at the address below and i will send you a
> workbook containing the code.
>
> --
> Cheers
> JulieD
> Excel MVP
>
> julied_ng at hctsReMoVeThIs dot net dot au
>
>
> "Jayant" wrote:
>
> > Hi,
> > By default this template is for year 2005. I need it for year 2006. Any idea
> > how to get, how to chage?
> > Thanks

Hi Jayant

Glad to hear that it worked for you.

--
Cheers
JulieD

julied_ng at hctsReMoVeThIs dot net dot au


"Jayant" wrote:

> Thanks Julie.
> I was successful.
> Excellent.
>
> "JulieD" wrote:
>
> > Hi Jayant
> >
> > Here's some code that will generate this type of schedule for any year and
> > any number of employees.
> >
> > 'start code
> >
> > Sub create_schedule()
> > 'written by Julie Dall 2006
> >
> > Dim for_year As Long 'year to create schedule for
> > Dim num_emp As Long 'number of employees to cater for
> > Dim i, j, k, m As Long
> >
> > 'populate variables
> > for_year = InputBox("Enter the year to create the schedule for",
> > "Year?", Format(Now, "yyyy"))
> > num_emp = InputBox("Enter the number of employees to cater for", "Number
> > Employees", 10)
> >
> > Application.Workbooks.Add 'add a new workbook
> >
> > 'create schedule
> > ActiveSheet.Range("A1").Select
> >
> > ActiveCell.Value = "Employee Absence Schedule for " & for_year
> > ActiveCell.Font.Bold = True
> > ActiveCell.Font.ColorIndex = 36
> > ActiveCell.Interior.ColorIndex = 53
> >
> > For i = 1 To 12
> > 'put months in
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value =
> > Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value =
> > "Employee Name"
> > 'format
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > True
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > 0).Font.ColorIndex = 36
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > 0).Interior.ColorIndex = 53
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > True
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > 0).Interior.ColorIndex = 19
> > For k = 1 To num_emp + 1
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > 0).Interior.ColorIndex = 19
> > Next
> >
> > 'put day header in
> > For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value =
> > Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > j).Interior.ColorIndex = 19
> > 'format
> > For k = 0 To num_emp + 1
> > If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > j).Value = "S" Then
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > j).Interior.ColorIndex = 19
> > End If
> > Next
> > 'put days in
> > For m = 1 To num_emp + 1
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m,
> > j).Value = Format(DateSerial(for_year, i, j), "d")
> > Next
> > Next
> > Next
> >
> > 'Fit columns
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > Selection.Columns.AutoFit
> > Range("A1").Select
> > Columns(1).HorizontalAlignment = xlJustify
> >
> > MsgBox "Finished"
> >
> > End Sub
> >
> > 'end code
> >
> > this code can be copied & pasted into the code module of a workbook (line
> > breaks removed) and when run, it will create a new workbook for you with the
> > schedule information.
> >
> > If you would prefer, email me at the address below and i will send you a
> > workbook containing the code.
> >
> > --
> > Cheers
> > JulieD
> > Excel MVP
> >
> > julied_ng at hctsReMoVeThIs dot net dot au
> >
> >
> > "Jayant" wrote:
> >
> > > Hi,
> > > By default this template is for year 2005. I need it for year 2006. Any idea
> > > how to get, how to chage?
> > > Thanks

I was unable to get the macro to run and received;
Compile error:
Syntax error
when I ran the macro below. I'm using excel 2003, any ideas??

"Jayant" wrote:

> Thanks Julie.
> I was successful.
> Excellent.
>
> "JulieD" wrote:
>
> > Hi Jayant
> >
> > Here's some code that will generate this type of schedule for any year and
> > any number of employees.
> >
> > 'start code
> >
> > Sub create_schedule()
> > 'written by Julie Dall 2006
> >
> > Dim for_year As Long 'year to create schedule for
> > Dim num_emp As Long 'number of employees to cater for
> > Dim i, j, k, m As Long
> >
> > 'populate variables
> > for_year = InputBox("Enter the year to create the schedule for",
> > "Year?", Format(Now, "yyyy"))
> > num_emp = InputBox("Enter the number of employees to cater for", "Number
> > Employees", 10)
> >
> > Application.Workbooks.Add 'add a new workbook
> >
> > 'create schedule
> > ActiveSheet.Range("A1").Select
> >
> > ActiveCell.Value = "Employee Absence Schedule for " & for_year
> > ActiveCell.Font.Bold = True
> > ActiveCell.Font.ColorIndex = 36
> > ActiveCell.Interior.ColorIndex = 53
> >
> > For i = 1 To 12
> > 'put months in
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value =
> > Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value =
> > "Employee Name"
> > 'format
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > True
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > 0).Font.ColorIndex = 36
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > 0).Interior.ColorIndex = 53
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > True
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > 0).Interior.ColorIndex = 19
> > For k = 1 To num_emp + 1
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > 0).Interior.ColorIndex = 19
> > Next
> >
> > 'put day header in
> > For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value =
> > Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > j).Interior.ColorIndex = 19
> > 'format
> > For k = 0 To num_emp + 1
> > If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > j).Value = "S" Then
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > j).Interior.ColorIndex = 19
> > End If
> > Next
> > 'put days in
> > For m = 1 To num_emp + 1
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m,
> > j).Value = Format(DateSerial(for_year, i, j), "d")
> > Next
> > Next
> > Next
> >
> > 'Fit columns
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > Selection.Columns.AutoFit
> > Range("A1").Select
> > Columns(1).HorizontalAlignment = xlJustify
> >
> > MsgBox "Finished"
> >
> > End Sub
> >
> > 'end code
> >
> > this code can be copied & pasted into the code module of a workbook (line
> > breaks removed) and when run, it will create a new workbook for you with the
> > schedule information.
> >
> > If you would prefer, email me at the address below and i will send you a
> > workbook containing the code.
> >
> > --
> > Cheers
> > JulieD
> > Excel MVP
> >
> > julied_ng at hctsReMoVeThIs dot net dot au
> >
> >
> > "Jayant" wrote:
> >
> > > Hi,
> > > By default this template is for year 2005. I need it for year 2006. Any idea
> > > how to get, how to chage?
> > > Thanks

There were some very long lines in the code that wrapped to the next physical
line. I corrected those line wraps and the code worked fine for me:

Option Explicit
Sub create_schedule()
'written by Julie Dall 2006

Dim for_year As Long 'year to create schedule for
Dim num_emp As Long 'number of employees to cater for
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long

'populate variables
for_year = InputBox("Enter the year to create the schedule for", _
"Year?", Format(Now, "yyyy"))
num_emp = InputBox("Enter the number of employees to cater for", _
"Number Employees ", 10)

Application.Workbooks.Add 'add a new workbook

'create schedule
ActiveSheet.Range("A1").Select

ActiveCell.Value = "Employee Absence Schedule for " & for_year
ActiveCell.Font.Bold = True
ActiveCell.Font.ColorIndex = 36
ActiveCell.Interior.ColorIndex = 53

For i = 1 To 12
'put months in
ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value _
= Format(DateSerial(for_year, i, 1), "mmmm yyyy")
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value _
= "Employee Name"

'format
ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold _
= True
ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0) _
.Font.ColorIndex = 36
ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0) _
.Interior.ColorIndex = 53
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0) _
.Font.Bold = True
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0) _
.Interior.ColorIndex = 19
For k = 1 To num_emp + 1
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k, 0) _
.Interior.ColorIndex = 19
Next

'put day header in
For j = 1 To Day(DateSerial(for_year, i + 1, 0))
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value _
= Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j) _
.Interior.ColorIndex = 19

'format
For k = 0 To num_emp + 1
If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j) _
.Value = "S" Then
ActiveCell.Offset(i + 2 + ((i - 1) * _
(num_emp + 3)) + k, j).Interior.ColorIndex = 19
End If
Next
'put days in
For m = 1 To num_emp + 1
ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m, j) _
.Value = Format(DateSerial(for_year, i, j), "d")
Next
Next
Next

'Fit columns
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Columns.AutoFit
Range("A1").Select
Columns(1).HorizontalAlignment = xlJustify

MsgBox "Finished"

End Sub


If it doesn't work, you'll want to be more specific about which lines cause the
errors.



LDP wrote:
>
> I was unable to get the macro to run and received;
> Compile error:
> Syntax error
> when I ran the macro below. I'm using excel 2003, any ideas??
>
> "Jayant" wrote:
>
> > Thanks Julie.
> > I was successful.
> > Excellent.
> >
> > "JulieD" wrote:
> >
> > > Hi Jayant
> > >
> > > Here's some code that will generate this type of schedule for any year and
> > > any number of employees.
> > >
> > > 'start code
> > >
> > > Sub create_schedule()
> > > 'written by Julie Dall 2006
> > >
> > > Dim for_year As Long 'year to create schedule for
> > > Dim num_emp As Long 'number of employees to cater for
> > > Dim i, j, k, m As Long
> > >
> > > 'populate variables
> > > for_year = InputBox("Enter the year to create the schedule for",
> > > "Year?", Format(Now, "yyyy"))
> > > num_emp = InputBox("Enter the number of employees to cater for", "Number
> > > Employees", 10)
> > >
> > > Application.Workbooks.Add 'add a new workbook
> > >
> > > 'create schedule
> > > ActiveSheet.Range("A1").Select
> > >
> > > ActiveCell.Value = "Employee Absence Schedule for " & for_year
> > > ActiveCell.Font.Bold = True
> > > ActiveCell.Font.ColorIndex = 36
> > > ActiveCell.Interior.ColorIndex = 53
> > >
> > > For i = 1 To 12
> > > 'put months in
> > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value =
> > > Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value =
> > > "Employee Name"
> > > 'format
> > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > > True
> > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > > 0).Font.ColorIndex = 36
> > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > > 0).Interior.ColorIndex = 53
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > > True
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > 0).Interior.ColorIndex = 19
> > > For k = 1 To num_emp + 1
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > > 0).Interior.ColorIndex = 19
> > > Next
> > >
> > > 'put day header in
> > > For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value =
> > > Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > j).Interior.ColorIndex = 19
> > > 'format
> > > For k = 0 To num_emp + 1
> > > If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > j).Value = "S" Then
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > > j).Interior.ColorIndex = 19
> > > End If
> > > Next
> > > 'put days in
> > > For m = 1 To num_emp + 1
> > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m,
> > > j).Value = Format(DateSerial(for_year, i, j), "d")
> > > Next
> > > Next
> > > Next
> > >
> > > 'Fit columns
> > > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > > Selection.Columns.AutoFit
> > > Range("A1").Select
> > > Columns(1).HorizontalAlignment = xlJustify
> > >
> > > MsgBox "Finished"
> > >
> > > End Sub
> > >
> > > 'end code
> > >
> > > this code can be copied & pasted into the code module of a workbook (line
> > > breaks removed) and when run, it will create a new workbook for you with the
> > > schedule information.
> > >
> > > If you would prefer, email me at the address below and i will send you a
> > > workbook containing the code.
> > >
> > > --
> > > Cheers
> > > JulieD
> > > Excel MVP
> > >
> > > julied_ng at hctsReMoVeThIs dot net dot au
> > >
> > >
> > > "Jayant" wrote:
> > >
> > > > Hi,
> > > > By default this template is for year 2005. I need it for year 2006. Any idea
> > > > how to get, how to chage?
> > > > Thanks

--

Dave Peterson

Thanks Dave; it worked first time, you have the touch!!

"Dave Peterson" wrote:

> There were some very long lines in the code that wrapped to the next physical
> line. I corrected those line wraps and the code worked fine for me:
>
> Option Explicit
> Sub create_schedule()
> 'written by Julie Dall 2006
>
> Dim for_year As Long 'year to create schedule for
> Dim num_emp As Long 'number of employees to cater for
> Dim i As Long
> Dim j As Long
> Dim k As Long
> Dim m As Long
>
> 'populate variables
> for_year = InputBox("Enter the year to create the schedule for", _
> "Year?", Format(Now, "yyyy"))
> num_emp = InputBox("Enter the number of employees to cater for", _
> "Number Employees ", 10)
>
> Application.Workbooks.Add 'add a new workbook
>
> 'create schedule
> ActiveSheet.Range("A1").Select
>
> ActiveCell.Value = "Employee Absence Schedule for " & for_year
> ActiveCell.Font.Bold = True
> ActiveCell.Font.ColorIndex = 36
> ActiveCell.Interior.ColorIndex = 53
>
> For i = 1 To 12
> 'put months in
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value _
> = Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value _
> = "Employee Name"
>
> 'format
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold _
> = True
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0) _
> .Font.ColorIndex = 36
> ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0) _
> .Interior.ColorIndex = 53
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0) _
> .Font.Bold = True
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0) _
> .Interior.ColorIndex = 19
> For k = 1 To num_emp + 1
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k, 0) _
> .Interior.ColorIndex = 19
> Next
>
> 'put day header in
> For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value _
> = Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j) _
> .Interior.ColorIndex = 19
>
> 'format
> For k = 0 To num_emp + 1
> If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j) _
> .Value = "S" Then
> ActiveCell.Offset(i + 2 + ((i - 1) * _
> (num_emp + 3)) + k, j).Interior.ColorIndex = 19
> End If
> Next
> 'put days in
> For m = 1 To num_emp + 1
> ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m, j) _
> .Value = Format(DateSerial(for_year, i, j), "d")
> Next
> Next
> Next
>
> 'Fit columns
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.Columns.AutoFit
> Range("A1").Select
> Columns(1).HorizontalAlignment = xlJustify
>
> MsgBox "Finished"
>
> End Sub
>
>
> If it doesn't work, you'll want to be more specific about which lines cause the
> errors.
>
>
>
> LDP wrote:
> >
> > I was unable to get the macro to run and received;
> > Compile error:
> > Syntax error
> > when I ran the macro below. I'm using excel 2003, any ideas??
> >
> > "Jayant" wrote:
> >
> > > Thanks Julie.
> > > I was successful.
> > > Excellent.
> > >
> > > "JulieD" wrote:
> > >
> > > > Hi Jayant
> > > >
> > > > Here's some code that will generate this type of schedule for any year and
> > > > any number of employees.
> > > >
> > > > 'start code
> > > >
> > > > Sub create_schedule()
> > > > 'written by Julie Dall 2006
> > > >
> > > > Dim for_year As Long 'year to create schedule for
> > > > Dim num_emp As Long 'number of employees to cater for
> > > > Dim i, j, k, m As Long
> > > >
> > > > 'populate variables
> > > > for_year = InputBox("Enter the year to create the schedule for",
> > > > "Year?", Format(Now, "yyyy"))
> > > > num_emp = InputBox("Enter the number of employees to cater for", "Number
> > > > Employees", 10)
> > > >
> > > > Application.Workbooks.Add 'add a new workbook
> > > >
> > > > 'create schedule
> > > > ActiveSheet.Range("A1").Select
> > > >
> > > > ActiveCell.Value = "Employee Absence Schedule for " & for_year
> > > > ActiveCell.Font.Bold = True
> > > > ActiveCell.Font.ColorIndex = 36
> > > > ActiveCell.Interior.ColorIndex = 53
> > > >
> > > > For i = 1 To 12
> > > > 'put months in
> > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value =
> > > > Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value =
> > > > "Employee Name"
> > > > 'format
> > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > > > True
> > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > > > 0).Font.ColorIndex = 36
> > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > > > 0).Interior.ColorIndex = 53
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > > > True
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > > 0).Interior.ColorIndex = 19
> > > > For k = 1 To num_emp + 1
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > > > 0).Interior.ColorIndex = 19
> > > > Next
> > > >
> > > > 'put day header in
> > > > For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value =
> > > > Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > > j).Interior.ColorIndex = 19
> > > > 'format
> > > > For k = 0 To num_emp + 1
> > > > If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > > j).Value = "S" Then
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > > > j).Interior.ColorIndex = 19
> > > > End If
> > > > Next
> > > > 'put days in
> > > > For m = 1 To num_emp + 1
> > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m,
> > > > j).Value = Format(DateSerial(for_year, i, j), "d")
> > > > Next
> > > > Next
> > > > Next
> > > >
> > > > 'Fit columns
> > > > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > > > Selection.Columns.AutoFit
> > > > Range("A1").Select
> > > > Columns(1).HorizontalAlignment = xlJustify
> > > >
> > > > MsgBox "Finished"
> > > >
> > > > End Sub
> > > >
> > > > 'end code
> > > >
> > > > this code can be copied & pasted into the code module of a workbook (line
> > > > breaks removed) and when run, it will create a new workbook for you with the
> > > > schedule information.
> > > >
> > > > If you would prefer, email me at the address below and i will send you a
> > > > workbook containing the code.
> > > >
> > > > --
> > > > Cheers
> > > > JulieD
> > > > Excel MVP
> > > >
> > > > julied_ng at hctsReMoVeThIs dot net dot au
> > > >
> > > >
> > > > "Jayant" wrote:
> > > >
> > > > > Hi,
> > > > > By default this template is for year 2005. I need it for year 2006. Any idea
> > > > > how to get, how to chage?
> > > > > Thanks
>
> --
>
> Dave Peterson
>

I'm sure Julie is pleased that you got her code working.

LDP wrote:
>
> Thanks Dave; it worked first time, you have the touch!!
>
> "Dave Peterson" wrote:
>
> > There were some very long lines in the code that wrapped to the next physical
> > line. I corrected those line wraps and the code worked fine for me:
> >
> > Option Explicit
> > Sub create_schedule()
> > 'written by Julie Dall 2006
> >
> > Dim for_year As Long 'year to create schedule for
> > Dim num_emp As Long 'number of employees to cater for
> > Dim i As Long
> > Dim j As Long
> > Dim k As Long
> > Dim m As Long
> >
> > 'populate variables
> > for_year = InputBox("Enter the year to create the schedule for", _
> > "Year?", Format(Now, "yyyy"))
> > num_emp = InputBox("Enter the number of employees to cater for", _
> > "Number Employees ", 10)
> >
> > Application.Workbooks.Add 'add a new workbook
> >
> > 'create schedule
> > ActiveSheet.Range("A1").Select
> >
> > ActiveCell.Value = "Employee Absence Schedule for " & for_year
> > ActiveCell.Font.Bold = True
> > ActiveCell.Font.ColorIndex = 36
> > ActiveCell.Interior.ColorIndex = 53
> >
> > For i = 1 To 12
> > 'put months in
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value _
> > = Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value _
> > = "Employee Name"
> >
> > 'format
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold _
> > = True
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0) _
> > .Font.ColorIndex = 36
> > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0) _
> > .Interior.ColorIndex = 53
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0) _
> > .Font.Bold = True
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0) _
> > .Interior.ColorIndex = 19
> > For k = 1 To num_emp + 1
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k, 0) _
> > .Interior.ColorIndex = 19
> > Next
> >
> > 'put day header in
> > For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value _
> > = Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j) _
> > .Interior.ColorIndex = 19
> >
> > 'format
> > For k = 0 To num_emp + 1
> > If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j) _
> > .Value = "S" Then
> > ActiveCell.Offset(i + 2 + ((i - 1) * _
> > (num_emp + 3)) + k, j).Interior.ColorIndex = 19
> > End If
> > Next
> > 'put days in
> > For m = 1 To num_emp + 1
> > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m, j) _
> > .Value = Format(DateSerial(for_year, i, j), "d")
> > Next
> > Next
> > Next
> >
> > 'Fit columns
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > Selection.Columns.AutoFit
> > Range("A1").Select
> > Columns(1).HorizontalAlignment = xlJustify
> >
> > MsgBox "Finished"
> >
> > End Sub
> >
> >
> > If it doesn't work, you'll want to be more specific about which lines cause the
> > errors.
> >
> >
> >
> > LDP wrote:
> > >
> > > I was unable to get the macro to run and received;
> > > Compile error:
> > > Syntax error
> > > when I ran the macro below. I'm using excel 2003, any ideas??
> > >
> > > "Jayant" wrote:
> > >
> > > > Thanks Julie.
> > > > I was successful.
> > > > Excellent.
> > > >
> > > > "JulieD" wrote:
> > > >
> > > > > Hi Jayant
> > > > >
> > > > > Here's some code that will generate this type of schedule for any year and
> > > > > any number of employees.
> > > > >
> > > > > 'start code
> > > > >
> > > > > Sub create_schedule()
> > > > > 'written by Julie Dall 2006
> > > > >
> > > > > Dim for_year As Long 'year to create schedule for
> > > > > Dim num_emp As Long 'number of employees to cater for
> > > > > Dim i, j, k, m As Long
> > > > >
> > > > > 'populate variables
> > > > > for_year = InputBox("Enter the year to create the schedule for",
> > > > > "Year?", Format(Now, "yyyy"))
> > > > > num_emp = InputBox("Enter the number of employees to cater for", "Number
> > > > > Employees", 10)
> > > > >
> > > > > Application.Workbooks.Add 'add a new workbook
> > > > >
> > > > > 'create schedule
> > > > > ActiveSheet.Range("A1").Select
> > > > >
> > > > > ActiveCell.Value = "Employee Absence Schedule for " & for_year
> > > > > ActiveCell.Font.Bold = True
> > > > > ActiveCell.Font.ColorIndex = 36
> > > > > ActiveCell.Interior.ColorIndex = 53
> > > > >
> > > > > For i = 1 To 12
> > > > > 'put months in
> > > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Value =
> > > > > Format(DateSerial(for_year, i, 1), "mmmm yyyy")
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Value =
> > > > > "Employee Name"
> > > > > 'format
> > > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > > > > True
> > > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > > > > 0).Font.ColorIndex = 36
> > > > > ActiveCell.Offset(i + 1 + ((i - 1) * (num_emp + 3)),
> > > > > 0).Interior.ColorIndex = 53
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), 0).Font.Bold =
> > > > > True
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > > > 0).Interior.ColorIndex = 19
> > > > > For k = 1 To num_emp + 1
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > > > > 0).Interior.ColorIndex = 19
> > > > > Next
> > > > >
> > > > > 'put day header in
> > > > > For j = 1 To Day(DateSerial(for_year, i + 1, 0))
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)), j).Value =
> > > > > Left(Format(DateSerial(for_year, i, j), "ddd"), 1)
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > > > j).Interior.ColorIndex = 19
> > > > > 'format
> > > > > For k = 0 To num_emp + 1
> > > > > If ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)),
> > > > > j).Value = "S" Then
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + k,
> > > > > j).Interior.ColorIndex = 19
> > > > > End If
> > > > > Next
> > > > > 'put days in
> > > > > For m = 1 To num_emp + 1
> > > > > ActiveCell.Offset(i + 2 + ((i - 1) * (num_emp + 3)) + m,
> > > > > j).Value = Format(DateSerial(for_year, i, j), "d")
> > > > > Next
> > > > > Next
> > > > > Next
> > > > >
> > > > > 'Fit columns
> > > > > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > > > > Selection.Columns.AutoFit
> > > > > Range("A1").Select
> > > > > Columns(1).HorizontalAlignment = xlJustify
> > > > >
> > > > > MsgBox "Finished"
> > > > >
> > > > > End Sub
> > > > >
> > > > > 'end code
> > > > >
> > > > > this code can be copied & pasted into the code module of a workbook (line
> > > > > breaks removed) and when run, it will create a new workbook for you with the
> > > > > schedule information.
> > > > >
> > > > > If you would prefer, email me at the address below and i will send you a
> > > > > workbook containing the code.
> > > > >
> > > > > --
> > > > > Cheers
> > > > > JulieD
> > > > > Excel MVP
> > > > >
> > > > > julied_ng at hctsReMoVeThIs dot net dot au
> > > > >
> > > > >
> > > > > "Jayant" wrote:
> > > > >
> > > > > > Hi,
> > > > > > By default this template is for year 2005. I need it for year 2006. Any idea
> > > > > > how to get, how to chage?
> > > > > > Thanks
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Is there a way that when you change the names Example: Employee 1 to Bryce Shelton that it changes in every months