Hi. Looking into Filter Pivot Table ways. Saw we have Create Pivot table & Refresh…but no filter (add columns to either Rows or Values). I thought I saw a package I can download but can’t find it anymore. Also looking into VB Code…but don’t see values in here? Can anyone please help me? Thank you so much!
(Additional- do I just use an anchor base activity and hot keys to double click the cell next to Grand Total? Any ways I can do it in the background without necessarily opening up excel? Document is pretty hefty data wise)
Sub Adding_PivotFields()
'PURPOSE: Show how to add various Pivot Fields to Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(“PivotTable1”)
'Add item to the Report Filter
pvt.PivotFields(“Year”).Orientation = xlPageField
'Add item to the Column Labels
pvt.PivotFields(“Month”).Orientation = xlColumnField
'Add item to the Row Labels
pvt.PivotFields(“Account”).Orientation = xlRowField
'Position Item in list
pvt.PivotFields(“Year”).Position = 1
'Format Pivot Field
pvt.PivotFields(“Year”).NumberFormat = “#,##0”
'Turn on Automatic updates/calculations --like screenupdating to speed up code
pvt.ManualUpdate = False
I’ve put the VB code, not too familiar with it, but know some from UFT. In this code (in original post above) i don’t see something for the ‘Values’ field…any idea how I can implement that as a filter? I see Rows, Columns, etc…but not Values
I’ve got an excel sheet like the image below. The top row are column headers. Need to create a pivot table from this.From the pivot table, I’d like to put ‘OFF’ column in rows filter field and "NUM’ column in values filter field. I’m trying to do this in the backend instead of clicking in Excel per se. Thank you so much!
oPivotField=CType(oPivotTable.PivotFields(“name of column”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name=“Count_Of_ID”
If you want SUM de values:
oPivotField=CType(oPivotTable.PivotFields(“name of column”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
oPivotField.Name=“Sum_Of_ID”
I dont think they currently have an activity that filter excel pivot table for multiple values, but i developed a walkaround to filter for or exclude multiple values, check it out, see if it is useful FilterPivotTableExcel.zip (35.2 KB)
The top row are column headers. Need to create a pivot table from this.From the pivot table, I’d like to put ‘OFF’ column in rows filter field and "NUM’ column in values filter field. I’m trying to do this in the backend instead of clicking in Excel per se. Thank you so much!