Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel time sheet updating

    I have 22 people that complete a time sheet and save the time sheet each day on our network. (All the individual time sheets are saved separately). Each time sheet has a months worth of dates on it. When I run out of time sheets, each time sheet has to be updated in terms of the dates on the sheet. (for the entire month). This updating is for each of the 22 sheets.

    Is there a way to automate this so not all 22 have to be update separately?

    I appreciate any help you can provide.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,495
    Thanks
    7
    Thanked 220 Times in 208 Posts
    You could have a macro that you run against the sheets that exports the data to a common file and then clears the sheet to start again.
    What we really need is a sample sheet and a description of what you need to change at the end of the month.

    cheers, Paul

  4. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Baxter, Tommy_2014.xlsx

    There is one of these sheets in an individual file. There is a tab for each pay period. What has to be updated is the date. (Once someone completes all the time sheets that are made up to that point, they have no time sheets to complete until more are made.

    Thanks!

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Stryped,

    This code will create a new time sheet automatically based on the last date of the last time sheet compare to the current date. In this sample workbook, the last time sheet has a final date of 7/20/2014. Since that is less than the current date of 8/11/2014, a new time sheet will be created when the workbook opens from a template worksheet and then properly dated. You should never have to manually create a new worksheet to stay current. If, however, you wish to make time sheets in advance, a button on the Template sheet will let you automatically create a new sheet, again, properly dated. If you do not wish to use the second semi auto method, you can just hide the Template sheet altogether The Template sheet will remain the last sheet as it needs to be in that position and all of your formulas will remain intact. Just rename the new sheet to what ever you wish. No need for a master or reference book.

    HTH,
    Maud

    Place the semi auto code in a standard module.
    Code:
    Public Sub UpdateSheets()
    On Error GoTo errorhandler
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(Worksheets.Count)
        [b4] = Worksheets(Worksheets.Count - 2).[h56] + 1
        MsgBox "New time sheet created"
        ActiveSheet.Name = "New Month"
        Exit Sub
    errorhandler:
        MsgBox "There is already a sheet named 'New Month'.  Please rename it with a different name"
    End Sub
    Place the Auto code in ThisWorkbook module
    Code:
    Private Sub Workbook_Open()
    On Error GoTo errorhandler
    If Worksheets(Worksheets.Count - 1).[h56] < Date Then
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(Worksheets.Count)
        [b4] = Worksheets(Worksheets.Count - 2).[h56] + 1
        MsgBox "New time sheet created"
        ActiveSheet.Name = "New Month"
    End If
    Exit Sub
    errorhandler:
    MsgBox "There is already a sheet named 'New Month'.  Please rename it with a different name"
    End Sub
    I will post the workbook momentarily from another computer

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    From the above post
    Attached Files Attached Files

  7. #6
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    From the above post
    Thanks so much. This is great!

  8. #7
    New Lounger
    Join Date
    Dec 2013
    Posts
    13
    Thanks
    1
    Thanked 3 Times in 3 Posts
    Hi Maudibe,

    Very slick, as usual. I do see a problem, however. Some "months" have 4 weeks instead of 5, and it's not clear from Stryped's example worksheet how to determine when a month will have 5 vs. 4 weeks. Your code creates only 5-week months, and relies on the previous month being a 5-week month (because you are picking up the date from H56).

  9. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    THill,

    Definitely on top of your game! Nice pickup.

    Stryped,

    I amended the code not to assume that the previous time sheet had 5 weeks but rather to look for the date of the last Sunday to determine the start date for the new time sheet. Also added is an input box to prompt for the number or weeks for the new time sheet then it is built accordingly. Like the last example, I could have consolidated parts of the 2 codes into one but left each separate so you could follow it easier.

    Thanks,
    Maud

    Place the semi auto code in a standard module.
    Code:
    Public Sub UpdateSheets()
    On Error GoTo errorhandler
    '---------------------------------------
    'DECLARE AND SET VARIABLES
    Dim dt As Date
    Dim LastRow As Integer
    Dim Line As Integer
    '---------------------------------------
    'GET LAST DATE OF PREVIOUS SHEET
        With Worksheets(Worksheets.Count - 1)
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        If LastRow >= 15 Then dt = .[h4]
        If LastRow >= 28 Then dt = .[h17]
        If LastRow >= 41 Then dt = .[h30]
        If LastRow >= 54 Then dt = .[h43]
        If LastRow >= 67 Then dt = .[h56]
    try_again:
    '---------------------------------------
    'GET NUMBER OF WEEKS FOR NEW SHEET
        num = InputBox("Enter the number of weeks on the new sheet (1-5).")
        Select Case num
            Case 1
                Line = 16
            Case 2
                Line = 29
            Case 3
                Line = 42
            Case 4
                Line = 55
            Case 5
                Line = 68
            Case ""
                Exit Sub
            Case Else
                MsgBox "You must select 1-5 weeks. Try again."
                GoTo try_again
        End Select
    '--------------------------------------
    'CREATE NEW SHEET
        Sheets("Template").Select
        Sheets("Template").Copy Before:=Sheets(Worksheets.Count)
        [b4] = dt + 1
        ActiveSheet.Name = "New Month"
        Range(Cells(Line, 1), Cells(68, 9)).Clear
        MsgBox "New time sheet created"
        End With
        Exit Sub
    errorhandler:
        MsgBox "There is already a sheet named 'New Month'.  Please rename it with a different name"
    End Sub
    Place the Auto code in ThisWorkbook module
    Code:
    Private Sub Workbook_Open()
    On Error GoTo errorhandler
    '---------------------------------------
    'DECLARE AND SET VARIABLES
    Dim dt As Date
    Dim LastRow As Integer
    Dim Line As Integer
    '---------------------------------------
    'GET LAST DATE OF PREVIOUS SHEET
        With Worksheets(Worksheets.Count - 1)
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        If LastRow >= 15 Then dt = .[h4]
        If LastRow >= 28 Then dt = .[h17]
        If LastRow >= 41 Then dt = .[h30]
        If LastRow >= 54 Then dt = .[h43]
        If LastRow >= 67 Then dt = .[h56]
        If dt < Date Then
    try_again:
    '---------------------------------------
    'GET NUMBER OF WEEKS FOR NEW SHEET
            num = InputBox("A new time sheet needs to be created.  Enter the number of weeks on the new sheet (1-5).")
            Select Case num
                Case 1
                    Line = 16
                Case 2
                    Line = 29
                Case 3
                    Line = 42
                Case 4
                    Line = 55
                Case 5
                    Line = 68
                Case ""
                    MsgBox "OK...you can create a new time sheet later from the Template sheet"
                    Exit Sub
                Case Else
                    MsgBox "You must select 1-5 weeks. Try again."
                    GoTo try_again
            End Select
    '--------------------------------------
    'CREATE NEW SHEET
            Sheets("Template").Select
            Sheets("Template").Copy Before:=Sheets(Worksheets.Count)
            [b4] = dt + 1
            ActiveSheet.Name = "New Month"
            Range(Cells(Line, 1), Cells(68, 9)).Clear
            MsgBox "New time sheet created"
            Exit Sub
        End If
        End With
    errorhandler:
    MsgBox "There is already a sheet named 'New Month'.  Please rename it with a different name"
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-08-14 at 22:03.

  10. #9
    New Lounger
    Join Date
    Dec 2013
    Posts
    13
    Thanks
    1
    Thanked 3 Times in 3 Posts
    Hi Maudibe,

    Very nice—I always learn something by looking at your code.

    T

  11. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Thanks T

Posting Permissions

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