Excel Macro and Pivot Table, Part II

My boss asked the following for a macro that creates a pivot table in Excel.  "Is it possible to get 1st login and last log out of each day on the line to the right of totals for each day?"  What adds complexity is that there might not be the same # of rows each time for the pivot table, depending on how many employees actually worked that day.  See attachments:  one is the pivot table and one has the macro within it.  Thanks.

P.S. - And why is the first file so big?  Is it the Pivot table?

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
Is this what you had in mind:
Sub BuildPivot()
   Dim pc As PivotCache
   Dim PT As PivotTable
   Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "'" & ActiveSheet.Name & "'!" & Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
   Set PT = pc.CreatePivotTable(TableDestination:=ActiveSheet.Range("K3"), TableName:="PivotTable1", _
   With PT
      With .PivotFields("Userid")
         .Orientation = xlRowField
         .Position = 1
      End With
      With .PivotFields("Day")
         .Orientation = xlRowField
         .Position = 2
      End With
      .AddDataField .PivotFields("Hours"), "Total Hours", xlSum
      .AddDataField .PivotFields("Time"), "Earliest Time", xlMin
      With .DataPivotField
         .Orientation = xlColumnField
         .Position = 1
      End With
      .AddDataField .PivotFields("Time"), "Latest Time", xlMax
      .PivotFields("Total Hours").NumberFormat = "hh:mm"
      .PivotFields("Earliest Time").NumberFormat = "hh:mm"
      .PivotFields("Latest Time").NumberFormat = "hh:mm"
   End With
End Sub

Open in new window

rvfowler2Author Commented:
Thanks, tha texplains it.  I meant for it to be assigned to Excel and VB script.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.