I would like to have some vba script to do the following:

Based upon a named list (employee) of which the number of items can
vary, create a new calendar year for each employee in the range.

The calendar should look like this
dateserial/isoweeknum/dayvalue/date/employee

isoweeknum is a formula I already have to calculate the weeknum of a
date
The list should start with 1-1-200n and should end with 31-12-200n
A listbox will be used to ask the user the specific year.

All calendars should be in the same columns underneath eachother (so I
have only 1 header row)

Thanks,

Mark

Hi Mark,

Try the following and see if it does what you want. However, I do wonder why
you want the date in a column as serial when you have it as a date. Also you
say you have a formula for isoweeknum suggesting that you created the method
in some round-a-bout way. WEEKNUM is already an xl function.

The macro assumes that your Employee list is on Sheet1 and it will place the
calendar on sheet2. You will need to edit the first 2 lines of code after the
Dim statements if this is not right. You also said that the employee list is
named 'Employee' and if this is not correct then the third line will need
edititing. (Naming the list is not the way I would have done it but it will
work so I have left it as is otherwise I would need to know the column etc
where the list is.)

I have formatted the columns A, B & C to numeric and D to date which
includes the day of the week.

Depending on which day of the week you want as the first day you might have
to edit the parameter in the formulas for WEEKNUM and WEEKDAY. I have them
set with Monday as the first day. Look them up in Help for other options.

Also I have left all the formulas created by the macro in the worksheet. If
you don't want them then you can copy- paste special- values after the
macro has run. Can be included on the end of the macro if you want it.

I believe that I recently answered a VBA question for you regarding list
boxes or combo boxes and have assumed that you know how to create them and
transfer the value into the macro. In the interim it is easy to just edit the
year value to test the macro to see if it does what you want.

Sub Create_Calendars()

Dim wsSht1 As Worksheet 'Sheet1
Dim wsSht2 As Worksheet 'Sheet2
Dim rngEmp As Range 'Named employee list
Dim celEmp As Range 'Each cell in employee list
Dim rngNxtEmp As Range 'Next Emp cell
Dim rngNxtSerDate As Range 'Next serial date cell
Dim rngLastEmp As Range 'Last cell of each employee
Dim strYear As String 'Year for calendar
Dim dateStart As Date '1 Jan 200n
Dim dateEnd As Date '31 Dec 200n
Dim lastAutoFill As Single 'Used for last AutoFill

Set wsSht1 = Sheets("Sheet1") 'Sheet with employee names
Set wsSht2 = Sheets("Sheet2") 'Sheet with calendar

Set rngEmp = wsSht1.Range("Employee")

strYear = "2008" 'Replace with listbox selection

dateStart = DateValue("1 January " & strYear)
dateEnd = DateValue("31 December " & strYear)
lastAutoFill = dateEnd - dateStart

With wsSht2
.Cells.Clear 'Clears all existing data
.Cells(1, 1) = "dateserial"
.Cells(1, 2) = "isoweeknum"
.Cells(1, 3) = "dayvalue"
.Cells(1, 4) = "date"
.Cells(1, 5) = "employee"
.Columns("A:C").NumberFormat = "0"
.Columns("D:D").NumberFormat = "ddd dd mmm yyyy"
Range(.Cells(1, 1), .Cells(1, 5)).Font.Bold = True
End With

For Each celEmp In rngEmp
With wsSht2
'Assign variable to the next cell in employee column
Set rngNxtEmp = .Cells(Rows.Count, 5).End(xlUp).Offset(1, 0)
Set rngLastEmp = rngNxtEmp.Offset(lastAutoFill, 0)
rngNxtEmp = celEmp
rngNxtEmp.Offset(0, -1) = dateStart
rngNxtEmp.Offset(0, -2).FormulaR1C1 = "=WEEKDAY(RC[1],2)"
rngNxtEmp.Offset(0, -3).FormulaR1C1 = "=WEEKNUM(RC[2],2)"
rngNxtEmp.Offset(0, -4).FormulaR1C1 = "=RC[3]"
Set rngNxtSerDate = rngNxtEmp.Offset(0, -4)
Range(rngNxtSerDate, rngNxtEmp).AutoFill _
Destination:=Range(rngNxtSerDate, rngLastEmp), _
Type:=xlFillDefault
End With

Next celEmp

wsSht2.Select
Columns("A:E").Columns.AutoFit
Range("A1").Select

End Sub

Feel free to get back to me if you have any problems with it and if you
posted via the MS communities website then don't forget to answer the
question as to whether this has helped.

Regards,

OssieMac

PERFECT!

Thank you so much.

Mark