HELP! Code for Pivot table sum

D

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
I recorded a macro with one of the last steps being the creation of a pivot table. I want the data to be the sum but it is always doing the count. Can anyone remedy this situation?

Ive added only part of the entire macro.

Code: 
        "Sheet3!R1C1:R5925C6").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="deviceKey", _
        ColumnFields:="reason_text"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Duration").Orientation = _
        xlDataField
    Sheets("Downtime Reasons").Select
    ActiveWindow.SmallScroll Down:=-27
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveWindow.SmallScroll Down:=-60

End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Here is the entire macro.

Code: 
Sub SulzerTimeline()
'
' SulzerTimeline Macro
' Macro recorded 4/5/2011 by Pau Gasol
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Columns("D:D").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("F:F").Select
    Selection.Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("F:F").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("F:F").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Duration"
    With ActiveCell.Characters(Start:=1, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""""/"""",RC[-1]/3600)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""""/"""",(RC[-1]/3600))"
    Range("G2").Select
    Selection.ClearContents
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/3600"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]/3600)"
    Range("G2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Columns("G:G").Select
    Selection.NumberFormat = "0.00"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("D1").Select
    Selection.AutoFilter Field:=4, Criteria1:="down_enum"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Downtime Reasons"
    Sheets("Downtime Reasons").Select
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-12
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet3!R1C1:R5925C6").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="deviceKey", _
        ColumnFields:="reason_text"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Duration").Orientation = _
        xlDataField
    Sheets("Downtime Reasons").Select
    ActiveWindow.SmallScroll Down:=-27
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveWindow.SmallScroll Down:=-60
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Range("I1").Select
    Range("I1:J5972").Select
    ActiveWindow.SmallScroll Down:=-102
    Range("H375").Select
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-15
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Sheets.Add
    Sheets("Downtime Reasons").Select
    Selection.Copy
    Sheets("Sheet5").Select
    ActiveSheet.Paste
    Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "Downtime Top Issues"
    Range("C1").Select
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "PivotChart"
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Unaltered Data"
    Sheets("Downtime Top Issues").Select
    
    Sheets("Downtime Top Issues").Select
    Columns("C:C").Select
    Selection.NumberFormat = "[h]:mm:ss"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Duration"
    Range("C1").Select
    Selection.Font.Bold = True
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]/24)"
    Range("C2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Downtime Reason"
    Range("A1:C1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Sheets("Unaltered Data").Select
    Range("A1:F1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Line"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Start "
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "End"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "State"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Reason"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Duration"
    Range("C2").Select
    Sheets("Downtime Top Issues").Select
    ActiveWindow.SmallScroll Down:=-9
    Sheets("Downtime Reasons").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Downtime Top Issues").Select
    ActiveWindow.SmallScroll Down:=-18
    
    Sheets("Unaltered Data").Select
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]/24)"
    Range("G2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-21
    Application.CutCopyMode = False
    Selection.Copy
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "[h]:mm:ss"
    Sheets("PivotChart").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Range("B4").Select
    Selection.NumberFormat = "[h]:mm:ss"
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Reason", _
        ColumnFields:="Line"
    Sheets("Downtime Top Issues").Select
    Sheets("PivotChart").Select
    Range("A3").Select
    
End Sub
 
Upvote 0
try this:
Code: 
 ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]/3600)"
    Range("G2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Columns("G:G").Select
    Selection.NumberFormat = "0.00"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("D1").Select
    Selection.AutoFilter Field:=4, Criteria1:="down_enum"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Downtime Reasons"
    Sheets("Downtime Reasons").Select
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-12
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet3!R1C1:R5925C6").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="deviceKey", _
        ColumnFields:="reason_text"

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Duration").Orientation = _
        xlDataField
'********************new line
ActiveSheet.PivotTables("PivotTable1").PivotFields("Duration").Function = _
        xlSum

'*********************end
    Sheets("Downtime Reasons").Select
    ActiveWindow.SmallScroll Down:=-27
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveWindow.SmallScroll Down:=-60
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Range("I1").Select
    Range("I1:J5972").Select
    ActiveWindow.SmallScroll Down:=-102
    Range("H375").Select
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-15
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Sheets.Add
    Sheets("Downtime Reasons").Select
    Selection.Copy
    Sheets("Sheet5").Select
    ActiveSheet.Paste
    Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "Downtime Top Issues"
    Range("C1").Select
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "PivotChart"
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Unaltered Data"
    Sheets("Downtime Top Issues").Select
    
    Sheets("Downtime Top Issues").Select
    Columns("C:C").Select
    Selection.NumberFormat = "[h]:mm:ss"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Duration"
    Range("C1").Select
    Selection.Font.Bold = True
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]/24)"
    Range("C2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Downtime Reason"
    Range("A1:C1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Sheets("Unaltered Data").Select
    Range("A1:F1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Line"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Start "
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "End"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "State"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Reason"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Duration"
    Range("C2").Select
    Sheets("Downtime Top Issues").Select
    ActiveWindow.SmallScroll Down:=-9
    Sheets("Downtime Reasons").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Downtime Top Issues").Select
    ActiveWindow.SmallScroll Down:=-18
    
    Sheets("Unaltered Data").Select
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]/24)"
    Range("G2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-21
    Application.CutCopyMode = False
    Selection.Copy
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "[h]:mm:ss"
    Sheets("PivotChart").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Range("B4").Select
    Selection.NumberFormat = "[h]:mm:ss"
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Reason", _
        ColumnFields:="Line"
    Sheets("Downtime Top Issues").Select
    Sheets("PivotChart").Select
    Range("A3").Select
    
End Sub
 
Upvote 0
Rich (BB code): 
'********************new line
ActiveSheet.PivotTables("PivotTable1").PivotFields("Duration").Function = _
ERROR ON THIS LINE        xlSum

'*********************end

Didn't work, error line is the one with the yellow text
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
318
RoryA
RoryA
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
252
Jette3173
J
U
  • Solved
Re-organizing pivot table fields in vba
Replies
2
Views
778
ummjay
U
C
  • Solved
VBA Pivot Table
Replies
2
Views
867
chaddres
C
tazeo
  • Question Question
VBA add row field not working
Replies
1
Views
957
ExcelPowerN
E

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top