Code for Pivot Charts

[tjgrassi]

I have a code for a user form that works well for the most part. It is a long code. I was wondering if there is any way to turn off a current Pivot Table Field that is selected, the only way I could figure out how to do it was turn each field off at the begining of the macro, this is the code I currently have to start my macro. I could run in to problems if someone where to try to change the pivot field manually and excel puts the new category in the row lable instead of values or if it was in values and not as a sum but as "count." I would need a code 3 times this long to cover all of the basis.

On Error Resume Next
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electric Intensity") _
.Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz (lbs)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz (Lbs)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink (Gallons)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water (Gallons)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas (Therms)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electric (kWh)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Output (Packages)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Output Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electricity Percent Change" _
).Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz Percent Change") _
.Orientation = xlHidden​

 

[pbornemeier]

I missed one change:

    Dim lPivotLineCount as Long ' starts with lower case l - because it is a Long variable
    lPivotLineCount = ActiveSheet.PivotTables(1).PivotRowAxis.PivotLines.Count

    ActiveSheet.PivotTables(1).PivotFields(Combobox2.Value).AutoSort _
        xlDescending, "Sum of " & Combobox2.Value, ActiveSheet.PivotTables(1).PivotRowAxis. _
        PivotLines(lPivotLineCount), 1

 

[tjgrassi]

I am now getting the error unable to get the error of unable to get the pivot fields property of the pivot table class. The error comes from this code. not sure if it would help but I am working with pivot table 7

 ActiveSheet.PivotTables(1).PivotFields(ComboBox2.Value).AutoSort _
        xlDescending, "Sum of " & ComboBox2.Value, ActiveSheet.PivotTables(1).PivotRowAxis. _
        PivotLines(lPivotLineCount), 1

 

[pbornemeier]

I believe that error will show if ComboBox2.Value is not an active field in your pivottable.

When the error occurs see what the value for ComboBox2.Value is.

If the value of ComboBox2 is "Bob"

Replace the code with:

ActiveSheet.PivotTables(1).PivotFields("Bob").AutoSort _
        xlDescending, "Sum of " & "Bob", ActiveSheet.PivotTables(1).PivotRowAxis. _
        PivotLines(lPivotLineCount), 1

and see if you get the same error.

If PivotTable7 is the only PivotTable on the ActiveSheet then PivotTables(1) is equivalent to PivotTables("PivotTable7")

 

[tjgrassi]

I get the same error as before. "unable to get pivotfields property of the pivottable class"

 

[pbornemeier]

Is that pivot field currently visible in your pivottable?

 

[tjgrassi]

When I put in to the code "central" for "bob" which is the one i tried to sort. I got an error on xlDescending saying invalid use of property.

 

[pbornemeier]

Turn on the macro recorder and sort "central". What is recorded?

 

[tjgrassi]

Range("G4").Select
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Site").AutoSort _
        xlDescending, "Sum of Water (Gallons)", ActiveSheet.PivotTables("PivotTable7"). _
        PivotColumnAxis.PivotLines(6), 1