Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have this code, that I found somewhere online and I have adjusted it for my
workbook, but I cannot seem to set up the data fields correctly. The macro takes a report of usage of a particular application and sums up the hours that person has been using it. Than it creates a pivot table with that data. That is where I am stuck. I can get the Pivot Table to create but I cannot seem to add an additional data filed. Here is what I have in pivot table: A1 = Count of Elapsed Time A2 = User name B2 = Total (numbers of hours spent in the application I want to add another data field called "Hour", so that it adds it in the cell B1. Below is the code I have, it works fine I just need it to add that other data field "Hour" Sub format() Dim LR As Long LR = Range("A" & Rows.Count).End(xlUp).Row Dim pt As PivotTable Dim strField As String Dim WSD As Worksheet Set WSD = Worksheets("base data") Dim PTOutput As Worksheet Set PTOutput = Worksheets("Sheet2") Dim PTCache As PivotCache Dim PRange As Range 'Insert Column headers into base data worksheet Range("G1").Select ActiveCell.FormulaR1C1 = "Hour" Range("H1").Select ActiveCell.FormulaR1C1 = "Day" 'Insert Formula for Hour and Day calculations Range("G2").Select ActiveCell.FormulaR1C1 = "=HOUR(RC[-6])" Range("H2").Select ActiveCell.FormulaR1C1 = "=DAY(RC[-7])" Range("H3").Select 'Formula was in G2 through N2, wanted to copy down G2 to column N Range("G2:N2").AutoFill Destination:=Range("G2:N" & LR) ' Find the last row with data Dim finalRow As Long finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row ' Find the last column with data Dim finalCol As Long finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column ' Find the range of the data Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol) Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=PRange) ' Create the pivot table Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutpu t.Cells(1, 1), _ TableName:="SamplePivot") ' Define the layout of the pivot table ' Set update to manual to avoid recomputation while laying out pt.ManualUpdate = True ' Set up the row fields pt.AddFields RowFields:=Array("User name") 'pt.AddFields ColumnFields:=Array("Hour") ' Set up the data fields With pt.PivotFields("Elapsed time") .Orientation = xlDataField .Function = xlCount .Position = 1 End With ' Now calc the pivot table pt.ManualUpdate = False End Sub Thanks for the help |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Data Fields in Pivot Table | Excel Discussion (Misc queries) | |||
data as a percetage of row fields in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Macro not inserting all data fields | Excel Discussion (Misc queries) | |||
how do i create pivot table from data fields containing forumulas | Excel Worksheet Functions | |||
Manipulating Pivot Table Data Fields | Charts and Charting in Excel |