Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way of taking information from two sheets and creating a merge of sorts to create individual spreadsheets? I am attaching the example I wish to use. I have an indirect labor timecard for the month of February that I want to create for each of my employees. The list of employees is on the second tab. For my real world work, this would be between 49 and 55 employees each month, so you can see why I would want to automate. I want to take the line item data for each employee off the second sheet and put it in the appropriate cells on the first sheet and create a new single worksheet with just the timecard for each employee (perhaps saved with the filename of the employee name as the file name. Updating the calendar for each new month, then running the macro to create all new timecards would be faster than creating ~50 new timecards individually. All I would have to do is maintain the employee list to make sure I get the right timecards created each month.

    Any help would be appreciated. I had considered trying this in Access with a report as well, but getting the calendar to work was a pain.

    Thanks

    Randy
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The following macro will create a directory on your desktop called TimeCards if it does not exist. It will then create a folder for the month contained in cell B7 on the Timecard sheet. It will then loop through the data on the Employees tab, insert the Name, ID and WC into the appropriate fields. Next, it saves each Timecard sheet to the TimeCards\Month directory using the employee's name as the file name.

    Code:
    Sub timeCards()
    Dim strDir As String, i As Integer
    Dim shtTime As Worksheet, shtEmp As Worksheet
    Set shtTime = Sheets("Timecard")
    Set shtEmp = Sheets("Employees")
    Application.ScreenUpdating = False
    strDir = Environ("userprofile") & "\desktop\TimeCards\"
        If Dir(strDir, vbDirectory) = "" Then
        MkDir (strDir)
    End If
    If Dir(strDir & Format(Cells(7, 2), "yyyy-mmm"), vbDirectory) = "" Then
        MkDir (strDir & Format(Cells(7, 2), "yyyy-mmm"))
    End If
    
    i = 2
    Do Until shtEmp.Cells(i, 1) = ""
        shtTime.Cells(3, 2) = shtEmp.Cells(i, 1)
        shtTime.Cells(5, 2) = shtEmp.Cells(i, 2)
        shtTime.Cells(3, 6) = shtEmp.Cells(i, 3)
        shtTime.Copy
        ActiveWorkbook.Close savechanges:=True, Filename:=strDir & _
            Format(Cells(7, 2), "yyyy-mmm") & "\" & shtEmp.Cells(i, 3)
        i = i + 1
    Loop
        shtTime.Cells(3, 2).ClearContents
        shtTime.Cells(5, 2).ClearContents
        shtTime.Cells(3, 6) = ""
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a slightly different approach that saves the timecard files in the same folder as the source file. Run the macro called "CreateSheets" by hitting alt+F8.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the assistance! It is appreciated as usual...

Posting Permissions

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