I am relatively new to Excel and am quite pleased with the progress I have made in adapting a timesheet given to me by a friend.
I have one sheet called the template which I manually copy one by one and edit to create a complete file for a year. Each worksheet is a fortnight. This is then distributed to staff for their use. It seems to work fine.
The problem is that it is reliant on me setting it up each year. Heavens! what happens if I should leave or get run over by a bus!!
I would like to automate it through the use of a button on the template that triggers a macro so that they can create a new years version without me. I know very little about VB and Macros and would appreciate some help.
In a tip I recently came across I found a macro that creates a workbook for each fortnight for a year. I thought hurray just what I need. Hmmm, it worked, creating Blank worksheets but I want to include the routine of copying the template.
Here it is:-
Sub YearWorkbook()
Dim iWeek As Integer
Dim sht As Variant
Dim sTemp As String
Dim dSDate As Date
sTemp = InputBox(“Date for the first worksheet:”, “End of Week?”)
dSDate = CDate(sTemp)
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count), _
Count:=(26 – Worksheets.Count)
For Each sht In Worksheets
sht.Name = Format(dSDate, “dd-mmm-yyyy”)
dSDate = dSDate + 14
Next sht
Application.ScreenUpdating = True
End Sub
Any assistance would be greatly appreciated. “Please remember I am new to Excel and this group – so be patient with me”. P l e a s e ?
Kerry
Australia
