VBA Pivot Table help with data fields

J

James

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:=xlDatabase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.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
 
D

Dave Peterson

When I'm creating this kind of thing, I'll record a macro when I do it
manually.

With all the options that you could be choosing from, I'm not sure anyone could
guess what you really want.

If you have trouble merging the recorded code into your existing code, post back
with that recorded code (and the current code if you changed it).
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:=xlDatabase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.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
Click to expand...
 
J

James

Here is the revised code with what I came up with (didn't work), and what I
recorded. What I recorded worked in a seperate module but it did not work
with my original code. I know it must be something small I am forgetting.

The new code is at the bottom. What I am trying to do is to add a column
field not a data field. I am not very good with these tables, and their
properties.

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:=xlDatabase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.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

'This is what I originally came up with, but didn't work
'With pt.PivotFields("Hour")
' .Orientation = xlColumnField
' .Position = 1

'This is what I recorded
' With ActiveSheet.PivotTables("SamplePivot").PivotFields("Hour")
' .Orientation = xlColumnField
' .Position = 1



End With

' Now calc the pivot table
pt.ManualUpdate = False

End Sub

Dave Peterson said:
When I'm creating this kind of thing, I'll record a macro when I do it
manually.

With all the options that you could be choosing from, I'm not sure anyone could
guess what you really want.

If you have trouble merging the recorded code into your existing code, post back
with that recorded code (and the current code if you changed it).
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:=xlDatabase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.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
Click to expand...
Click to expand...
 
J

James

Dave,
I figured out the problem. I was missing the "End With", it works now I
don't know how I missed that one.

Dave Peterson said:
When I'm creating this kind of thing, I'll record a macro when I do it
manually.

With all the options that you could be choosing from, I'm not sure anyone could
guess what you really want.

If you have trouble merging the recorded code into your existing code, post back
with that recorded code (and the current code if you changed it).
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:=xlDatabase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to fix a Dim 2
Pivot table 1
VBA code not working... 7
Loading pivot table problem 3
Link Pivot Tables on one filter 2
Pivot Table - 3
VBA Loop Help 2
Pivot tables - memory issues - please help 2

Top