Solved

Excel Macro and Pivot Table, Part II

Posted on 2011-02-18
4
598 Views
Last Modified: 2012-05-11
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?



LogonReport.xls
LogonReportStarter2.xls
0
Comment
Question by:rvfowler2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Author Comment

by:rvfowler2
ID: 34969998
Thanks, tha texplains it.  I meant for it to be assigned to Excel and VB script.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35017378
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", _
                                DefaultVersion:=xlPivotTableVersion10)
   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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question