Pivot Table sum not count!

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
The following macro creates a pivot table and the data is suppose to aggregate by sum not count but it is NOT COOPERATING!

Here is the macro which to my understanding tells the pivot table to do a sum of the data. Can anyone propose a corrected code or another string of code to make it do the sum?

Code:
 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
    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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

chall

New Member
Joined
May 15, 2009
Messages
12
Does your data set contain nulls? If so replace those with zeros.
 

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
Yes there are nulls because I have the macro auto select a range to create a pivot table from.


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet3!R1C1:R5925C6").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10


Is there a way to tell it to only select a range with populated cells?
 

chall

New Member
Joined
May 15, 2009
Messages
12

ADVERTISEMENT

Make the replacement in the source data, not the pivot report. There should be one data column to edit.
Dim rng As Range
Set rng = Sheet3.Range("h1:h100")
rng.Replace What:=Null, Replacement:=0
 

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
I get a Run time error 424, Object Required, this code is highlighted during debug.

The Set rng = Sheet3.Range("h1:h100")


I've tried to change the range to multiple things and I keep getting the same error.

Please Help!
 

chall

New Member
Joined
May 15, 2009
Messages
12

ADVERTISEMENT

Can you just manually select the data range and do a Home>Find>Replace to get rid of all the null or missing values that way? Be sure to have a backup of your data.
 

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
No, I need it to do it all in one step. I am going to eventually export these macros to a few co-workers who are in their late 50's early 60's that have very limited computer ability.

Hitting ctrl+shift+T and being able to look at the result is about as far as I'm willing to push them.

This macro is serviceable, i just have to select sum everytime it is finished but for utmost perfection/simplicity, I want the macro to do it automatically.
 

chall

New Member
Joined
May 15, 2009
Messages
12
Ok then, back to your error message. Be sure that Microsoft DAO Object library is checked in the references.
 

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
That didn't help either. Im to the point that I might say "screw it", hopefully double-clicking on a button and selecting sum is not too complicated...:eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,758
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top