?
Solved

Excel Macro and Pivot Table, Part II

Posted on 2011-02-18
4
Medium Priority
?
602 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

801 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