Timesheet HELP! Pivot table?

halieb · Jan 30, 2017

I am creating a time sheet for a client that would like to enter the time based on each employee's hours worked, but their head office would like the time input based on job number (see attachments). This may be a great time to utilize pivot tables but I am not too familiar with them. I have created a spreadsheet where my client can enter the hours based on each employee, what is the best way to automatically create a second table sorted by "job number"? I have attached pictures showing the format the head office is looking for as well as the format my client is already used to.

Thanks!
Halie

p45cal · Feb 20, 2017

try in your sample file:

Code:

Sub blah2()
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Range("A1:O1").Value = Array("Employee Name", "Employee #", "Classification", "Job Name", "Job Number", "SUN", "MON", "TUE", "WED", "THU", "FRI", "SAT", "Amount", "Expense Note", "Expense")
Set Destn = NewSht.Cells(2, 1)
With Worksheets("Employee Timesheets").Columns(1)
  Set c = .Find("Job Name", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  If Not c Is Nothing Then
    firstAddress = c.Address
    Do
      If c.CurrentRegion.Rows.Count > 1 Then
        Set mySceDataRng = Intersect(c.CurrentRegion, c.CurrentRegion.Offset(1))
        Destn.Resize(mySceDataRng.Rows.Count, 3).Value = Application.Transpose(c.Offset(-4, 1).Resize(3))
        With mySceDataRng
          Destn.Offset(, 3).Resize(mySceDataRng.Rows.Count, 2).Value = .Resize(, 2).Value
          Destn.Offset(, 5).Resize(mySceDataRng.Rows.Count, 7).Value = .Offset(, 3).Resize(, 7).Value
          Destn.Offset(, 12).Resize(mySceDataRng.Rows.Count, 3).Value = .Offset(, 12).Resize(, 3).Value
          Set Destn = Destn.Offset(mySceDataRng.Rows.Count)
        End With
      End If
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
  End If
End With
End Sub

Timesheet HELP! Pivot table?

halieb

New member

Attachments

p45cal

Super Moderator