Excel 2010 :: Changing Pivot Table Report Filter Via VBA With Named Range Or Array

Jul 21, 2011

I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.

My code thus far:

Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

[Code]....

emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").

I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.

When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:

For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False

When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.

Here's the corresponding code for that:

For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next

I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).

I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.

(Using Excel 2010, Windows 7.)

View 9 Replies


ADVERTISEMENT

Excel 2010 :: Pivot Table Add Calculated Field Based On Certain Text In Report Filter

May 29, 2014

Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.

I used the formula below in a calculated field and it does not match the values using the added column to the data file.

=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)

I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.

View 2 Replies View Related

Excel 2010 :: Report Filter For Pivot Not Working With VBA?

Apr 5, 2013

I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)

The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.

When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line

Error Message: Unable to set the Visable Property of the Pivot Item Class

Code:
Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#

[Code]....

View 9 Replies View Related

Excel 2007 :: One Pivot Table - Multiple Graphs Based On Report Filter?

Jan 10, 2014

I have a perfectly working pivot table and I would like to make some graphs based on the report filter. My report filter has 4 categories, with each more than 10 sublevels.

When I make one pivot graph/chart, this goes fine, the data is ok, and I am happy. But one I make a second, and thus adjust the report filter, the first graphs changes according to the filter. I dont want that to happen

Ultimately I would like a powerpoint presentation with multiple charts, based on one table, with different report filter filters. Updated ONLY on the values, not the filter.

View 4 Replies View Related

Filter OLAP Pivot Table Based On Named Range In Another Worksheet

Mar 14, 2012

I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.

Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.

In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.

Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...

'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem

[Code]....

View 4 Replies View Related

Excel 2010 :: Pivot Table Filter Query

Feb 21, 2012

I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.

However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed).

Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it?

View 4 Replies View Related

Excel 2010 :: Pivot Table Dropdown Filter Limited To Records?

Aug 12, 2010

In Excel 2010; the pivot Tables drop down filter is limited to 10,000 records. if more than 10,000 are available then a message saying "Not all items showing" is displayed at the bottom of the list.

Clickin on the message would display a window saying: "This field has more than 10,000 items under one or more parent items. Only the first 10,000 items are displayed under each parent item."

View 3 Replies View Related

Excel 2010 :: Pivot Table Dropdown Filter Hiding Items?

Dec 22, 2011

In the coming months the company i work for will be transferring from Excel 2003 to Excel 2010 (i know, a little late......) and now i am testing some things at home.

We deliver lots of Excel reports to our clients where we are using Pivot tables. In excel 2003 we where able to hide items from the dropdowns using properties - hide items but when i now open an excel 2003 file in excel 2010 and want to filter the pivot table to (let's say) another month i see all my hidden items.

Is there an option in excel 2010 to hide items like i could do in 2003? (either regular option or VBA)

View 4 Replies View Related

Excel 2010 :: Load Values From Named Range Into Array?

Jan 15, 2014

Code:
For Each clsName In Array("Africa", "Europe", "Asia")

If I have the above array values in the named range _lstRgns in Sheet 1 of my workbook, how would I write the code to pick this up?

The named range is expandable, as it is an offset formula in the name manager.

clsName is the name of my defined array in my code

View 1 Replies View Related

Locking Pivot Table But Not Report Filter?

Aug 29, 2013

So I was wondering if it were possible to completely lock a pivot table, almost like protecting the sheet, but to allow the report filter field at the top of the table to be used. So what I'm trying to show is our net rev accounts in the rows & quantity and amounts as the value all fixed and unable to move about or drill down; but then have our product titles at the top in the report filter which you can still use to be able to select specific products.

I understand that I could make a big old page of drop down lists and vlookups and sumifs etc etc, but surely the above would be way quicker, plus I wouldnt have to bother checking it all works regularly!

I'm currently running this macro, which nearly does the job, but the data and row labels are still able to be manipulated. So... changing the macro, or doing something completely different?

Sub KillPivot()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)

[Code]....

View 1 Replies View Related

VBA To Manipulate Pivot Table Report Filter

May 28, 2009

I have a pivot table created from a data table with three columns: Date, Sales, and Customers. I have the Date column in the Report Filter and I want to change the date based upon a value in a cell range named Date, of all things. The pivot table is located in another worksheet and the range Date is changed by a spin button in the active worksheet.

My code is:

Sub ptDate()

Dim pt As PivotTable
Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")

pt.PivotFields("Date").ClearAllFilters
pt.PivotFields("Date").CurrentPage = Range("Date")

End Sub

View 9 Replies View Related

Creating Custom Pivot Table Report Filter

Aug 8, 2012

I was wondering if it is possible to create a custom pivot table report filter? I would like to take an existing pivot table report filter and manually add values into it. I would like to do this because I have multiple pivot tables, some with the same values and some with different values and I have a VBA code from Contextures that applies a mass filter to all fields with the same name. So if i could manually add values into one report filter, I could filter from one location and have all my pivot tables update at the same time if they contain the value that i would like to filter by.

View 1 Replies View Related

Report Filter In Pivot Table Based On Another Field From The List?

Feb 20, 2012

Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.

View 2 Replies View Related

Pivot Table - Grouping Items (Date) In Report Filter?

Nov 11, 2013

I have a pivot table with the following items;

Report Filter = Project Names & Dates (filtered on 2 fields)

then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days

When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.

Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?

View 2 Replies View Related

List Box To Select Multiple Items In Report Filter Of Pivot Table?

Jun 19, 2013

I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.

When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)

View 2 Replies View Related

Change Pivot Table Report Filter From Userform Textbox / Combobox

Aug 17, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using combobox on userform.

Sample Data

ID
NAME, INIT
GENDER
DEPT
SALARY
DOH
LOCATION
RAISE

1
Smith, J.
F
Sales
$41,250.00
2/2/1982
Boston
$45,375.00

[Code] .....

VB:
Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Employees Data").Select
Range("A1").Select

[Code] .....

Error:
Private Sub ComboBox1_Change()
ComboBox1.Value = objTable.PivotFields("DOH")
End Sub

View 1 Replies View Related

VBA Code To Change Pivot Table (Report Filter) From Userform Textbox / Combobox

Aug 16, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. I've attached an excel file as an example.

View 2 Replies View Related

Pivot Table - Show Report Filter Pages Shortening Tab Names Even When Character Is 31

Mar 4, 2014

I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.

View 1 Replies View Related

Excel 2010 :: Hiding Rows In Pivot Table With Specific Value Range?

Jan 20, 2014

I am using a pivot table in excel 2010. 15 columns (fixed) and plenty of dive downs for rows.

I am trying to 'hide' all of the rows containing values less than 10. If it is easier we can start with hiding values of zero and go from there.

It is key to hide the rows as within the dive downs the rows within the pivot are to many to sort through. If there is a way to do this using the grand total columns for each row that would work as well. Just to be clear I need to hide the entire row not just report the empty cells as zeros. If any further detail is needed feel free to ask.

For example: If value of column O "Grand Total" = 0 'hide rows with value 0' ELSE 'display rows with value greater than 0'

View 4 Replies View Related

Array For Filter In Pivot Table?

Dec 11, 2012

How to use an array of multiple values ​​in code I want to use the array of values "jan" "feb" "mar" in the code instead of "jan"

Code:
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("Pivottable1").PivotCache.Refresh
ActiveSheet.PivotTables("Pivottable1").PivotFields("cat").ClearAllFilters
ActiveSheet.PivotTables("Pivottable1").PivotFields("cat").PivotFilters.Add _
Type:=xlCaptionDoesNotEqual, Value1:="jan"
End Sub

View 2 Replies View Related

Excel 2010 :: Pivot Table Reference Is Not Valid When Moving Data And Pivot Table Together?

Mar 19, 2013

On a worksheet, I created:

- a list of data
- a pivottable based on these data

When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.

This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.

View 3 Replies View Related

Excel 2010 :: (VBA) Pivot Table And Pivot Cache?

Mar 13, 2014

My macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.

New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.

Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.

My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.

Fix / Solution:

Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.

Code:

Dim VBAPPPC As PivotCache
Dim VBAAPPC As PivotCache
Dim VBAPRPC As PivotCache
Dim VBAEXPC As PivotCache
Dim VBAMJPC As PivotCache
Dim VBAIVPC As PivotCache
Dim VBAPT As PivotTable

[code]...

View 1 Replies View Related

Excel 2010 :: Create Sheet With Table From Pivot Table?

Apr 26, 2012

Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.

Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)

View 3 Replies View Related

Excel 2007 :: Pivot Table Row Labels To Named Worksheets?

Feb 11, 2013

I am using Excel 2007. I have a population that I used to create a pivot table. I am currently double clicking on the value cells to create worksheets of only particular "row label" categories. I am then copying the "row label" information into the newly generated work sheet name tab. This works fine when I only have a few "row label" categories to do but it is tedious if there are many categories.

Is there any way to automate the creation of work sheets for all row label values and also naming each work sheet tab with it's respective row label information. Here are images of the pibot table and the type of work sheet I would like for wall row label values.

View 1 Replies View Related

Excel 2007 :: Value Filter In Pivot Table

Oct 9, 2008

I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?

View 9 Replies View Related

Excel 2010 :: Filter Table Into Smaller Tables?

Jan 4, 2014

I have a table that contains all my data and would like to filter it by company into their own tables that will stay up to date with the main table and then hopefully somehow calculate their investment returns (future stage). I thought Microsoft Query would work but I came across a problem see my other post MS Query returning data to excel some columns I cant sum looks like text

Excel 2010

View 1 Replies View Related

Excel 2007 :: How To Filter Values In Pivot Table

May 31, 2013

In Excel 2007 is it possible to filter the "Values" in a Pivot Table?

I am tracking the Gross weight of shipping containers. My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight.

for example:

Row Labels Sum of Gross Wt. - Lbs
10003150588929
10003153258700
10003155984958
1000315651530
10003156549761

I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible.

View 3 Replies View Related

Macro To Pick Up A Range Of Date To Filter On In A Pivot Table

Jun 25, 2008

When using a Pivot Table I regularly filter on known data specific to me i.e. WBS codes.

Column A contains the WBS Codes which can be as many a 1000, however, I only need to filter out the 10 or so I require, and currently I uncheck all the records and manually scroll through the list to check the ones I require.

In order to speed things up I recorded a macro which works fine, however, If I need to check any new codes I would have to record the macro again, as I tried to edit the Macro and all it does is to un check all the other codes I don't need.

Is it possible to create a Macro that picks up a range of data that is then used to filter on. (i.e. only the records I require)

View 9 Replies View Related

Excel 2011 :: Display Filter Values In Pivot Table

Jan 17, 2014

I have a pivot table which has a report filter. The report filter can have anywhere from 20 to 350 values. The user will be able to select multiple values from the listing to produce the table as needed. I would like to be able to display what values were selected in the report filter so that the user can see this information once the pivot table is rendered. Is there any way to display this information either above below or along side of the table itself?

I am using Excel 2011 for Mac, but I also have access to Excel 2011 for Windows. Either way will work. I have been able to get quite a bit of what works on Windows to work with the Mac.

View 1 Replies View Related

Excel 2011 :: How To Filter Pivot Table With Multiple Values On Mac

Apr 17, 2014

I'm on Mac using Excel 2011. This means I don't have "slicers", which is all my Googling kept turning up.

I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. remove everything below a specific number in one column, and filter for only specific strings in another column, etc.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved