Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Macro help for Timesheet (Excel 97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    Assuming that your template sheet is named "Template", and that it is in the same workbook as the macro and that the new worksheets will be created in this same workbook, I would do it like this:

    <pre>Public Sub CreateSheets()
    Dim I As Long
    Dim oNewSheet
    Dim datDate
    Dim strStartDate
    Application.ScreenUpdating = False
    strStartDate = InputBox("Date for the first worksheet:", "End of Week?")
    If strStartDate = "" Then
    Exit Sub
    End If
    datDate = CDate(strStartDate)
    If datDate = 0 Then
    MsgBox "Invalid date entered"
    Exit Sub
    End If
    For I = 1 To 26
    Set oNewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oNewSheet.Name = Format(datDate, "dd-mm-yyyy")
    Worksheets("Template").Cells.Copy
    oNewSheet.Paste Destination:=oNewSheet.Range("A1")
    datDate = datDate + 14
    Next I
    Worksheets(1).Activate
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    Lagare thank you for your assistance. This did exactly what I had asked, however, I had not given it a thought until I ran this Macro that each sheet has to have a new fortnight starting date at cell N3. This being the same date as the Worksheet Tab.

    Also a starting amount of hours (Positive or Negative) is carried forward from a sheet called the Master and form there on carried forward to the next worksheet. Is it possible to include these two actions into the same routine?

    I have attached the file in the hope that what I am describing will make more sense.

    I have also considered that perhaps the Master and Template could be one in the same.

    Opinions are welcome.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    this is a file I use for our flexitime system.
    The fomula (+ code) for cells c1 & b10 in period2/3 might help you towards what you want
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    Thanks Bryan - I will take a look at your example.

    Kerry

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    The attached workbook has the macro modified to insert the start date in cell N3 on each new sheet added.

    I'm not sure I understand your second question. From what cell on the master is the hours carried forward, and to what cell on the first new sheet is it carried forward to? From what cell on each inserted sheet is the hours carried forward from and to what cell on the next sheet? Should the hours be carried forward as a constant amount, or as a formula so that if the previous sheet is changed the change carries forward automatically?
    Attached Files Attached Files
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    P15 on Master is carried forward to P26 On Template (example =Master!P15) - There after P26 on each sheet takes forward the total from P27 on the previous sheet. (example ='22 Feb - 7 Mar'!P27).

    This is quite combersome, but I know no better way to do this.

    Bryan - who sent the other response has an interesting formula on his spreadsheet, but unfortunately I am unable to interpret it.

    As I said earlier, I am no whizz with Excel. I just know what I want, but not always how to do it.

    Kerry

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    See if this does what you want.
    Attached Files Attached Files
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    Sorry Kerry, I should really have put some explanation with the posting.

    If you look at cell B10 in the period 2 worksheet it has the formula
    =INDIRECT(GetPrev(A1)&"!M28")
    GetPrev is a function I developed (with help from this site if I recall) which returns the name of the previous worksheet - in this case "period 1". Ignore the A1 the function needs a cell address any one will do. So with that value "period 1" the formula now reads
    =INDIRECT("period 1!M28")
    which brings the carrry forward value from the period 1 sheet into period 2. Take a copy of period 2 and call it period 3 and the cell formula is copied but now returns period 2 as the previous worksheet. And so on - each copy you make it returns the previous sheet but the formula stays the same.
    This gives the same result as copying the sheet then manually editing the formula in each one to read period 2, period 3 etc.
    In cell C1 it uses the same fornula but adds 28 to move the start date to the next 4 week period.

    hope this makes it a bit easier to figure out.

  10. #10
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    This is just what I was looking for Lagare. Thankyou very much. I am very excited about finalising this project that has been bugging me for ages. I will be free to work on other tasks on my long list!

    I feel priviledged to have your help and I hope I am not stretching things too much by asking just another question?

    Can you tell me why, when the sheets are copied the new ones are not formatted the same as the template. That is, they show some (not all) gridlines and the zoom is at 100%?

    Also is it possible to protect each sheet in the Macro. If I leave the template protected before running the macro, the dates dont transpose correctly. So if I leave the Template unprotected, then so are all of the copies and they then need to be protected individually. I am reluctant to leave them unprotected as people always want to have a fiddle...

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    Hi Bryan

    Thankyou kindly for your explanation of the formula in your spreadsheet. It is obviously an option I could have considered.

    You may see that Legare has adjusted the macro to fix this for me and it works very well. You may find it useful too.

    Once again thank you. Isnt this site the best!! I have never come across such a helpful and friendly group. I hope I can offer someone help one day, instead of always asking the questions.

    Kind Regards
    Kerry

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    Here is a new workbook that I think does what you asked. It assumes that the Template sheet is protected with a password of "MyPassword" (without the quotes) and it will protect all of the new sheets with that same password. You will need to change the password in three places in the macro if you want to use a different password.
    Attached Files Attached Files
    Legare Coleman

  13. #13
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    I am almost too embarrased to ask, but here goes.

    I have tried to nut this one out myself, but everything I tried didnt work.

    When I looked at the Print Preview I found the copies created are in portrait orientation and the header does not copy over.

    I almost hear the sigh - "oh what next??"

    Kerry

    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    OK, the attached sheet should fix those problems. However, one warning. For some reason changing some of the pagesetup properties like page orientation are VERY slow. The macro with these changes runs MUCH longer than the previous versions.
    Attached Files Attached Files
    Legare Coleman

  15. #15
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Macro help for Timesheet (Excel 97)

    I just wanted to thank you for your help. I have the timesheet working now and although the Macro runs very slowly it does the job.

    The best part of it all is that I actually learnt something. I feel much more confident about having a go myself and am inspired to do more with Excel.

    Kind Regards
    Kerry

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •