I would like to program to hide pivot items given certain conditions. For example, automatically show items between range 10 to 20, hide all others.
Secondly, I would like to show the top and bottom 10. The autoshow function can only list top 10 or bottom 10. I need to do both at the same time.
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)
I have a pivot that shows Location, Dept and Total Sales by month. So Location and Dept are in the row area, Period is in the column area and Total Sales are summed in the page area.
Well, they decide they do not want to see Dept: 999 on the pivot. Thats easy, just click the dropdown and uncheck it....they are amazed at my ingenuity!
Well, they then notice Dept: 999 is no longer included in the Location total or grand total....makes sesne to me I say!
Well, they then specify that Dept: 999 should NOT appear on the pivot but SHOULD be included in subtotals / totals.
I've got 4 pivot tables (all derived from the same base data) on 4 separate worksheets. I've been able to (with this help of this site) to use VBA to hide pivot items on all of these sheets using a list on a user form. Hide/Show Pivot Table Field Items. Hide Pivot Table Fields Pivot Items by Criteria
I now need to be able to show all the pivot items on only 3 of the 4 pivot tables, with the 4th pivot table being left untouched. For ease assume that my sheets are sheet1, sheet2, sheet3, and sheet4. The tables I wish to update are on sheet2, sheet3 and sheet4. The pivot table on each sheet is called "PivotTable4" and the pivot item is called "Business". The pivot item contains 12 business names (Business1, Business2 etc etc)
Is there an easy way of doing this? I've spent the day looking through the internet and various "Dummies" books but with little success, I fear that I'm obviously below even Dummy level
I have a macro that produces pivot tables, hiding the blanks in the process. It has failed today for the first time, which I think is because there were no blanks, so nothing to hide....
How can I fix the code so blanks are hidden only if necessary...
Code:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Email or Call")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("wk_range")
.PivotItems("(blank)").Visible = False
End With
I'm making a pivot with 10.000 employees and their average salaries by cost centre. If the number of employees in one cost centre is below 5, the information should be hidden.The plan would with VBA to check if the cost centre subtotal in the pivot is below 5 and hide then all the employees belonging to this cost centre.
View 9 Replies View RelatedWhenever I click on a pivot table, the pivot table toolbar pops up. Generally, I like this, but it confuses my users of this workbook. Is there a way to hide the toolbar just for this workbook?
View 3 Replies View RelatedI have a question regarding pivot tables. how do I hide a field but still maintain that filter? I have a field in the Column Fields titled "International". Right now I've filtered out only the YES entries, but I don't want to see a "YES" column beside each. When I try to hide the column the filter is reset and all entries, international or not, are seen. Can I hide the column but maintain its filter?
View 6 Replies View RelatedI am looking to hide or delete the extra worksheet that is created when creating a Pivot table Macro. When I create a Pivot Table, it creates Sheet1 with it blank.
Is there a code that I can add to hide or delete the blank worksheet (sheet1)? Each workbook that the macro is distubuted in has a different workbook name, ex South_Report.XLSM, West_Report.XLSM.
Context: I'm pulling a data set "A" into excel from MS SQL (currently 48,000 rows ... comes back in < 10s). I will add a field "Cost" to data set "A" that uses info from that data set to look up a value in data set "B" which is in another Excel Sheet and perform a calculation. The sheet that contains data set "B" may or may not be in the same file - haven't decided yet but I'm not really concerned about that.
I actually have the calculated "Cost" field added to the end of data set "A". When I refresh the data, the fomulas recalculate.
My intent from here is to present the data in a pivot table. I will add a "Refresh Data" command button that:
1. Updates the data from MS SQL
2. Refreshes the pivot
I've done 1&2 many times before and have code I'll reuse.
The problem: I cannot present a pivot sourced straight off the amended data set "A" because it contains fields that should not be accessible by the audience for this report (as a matter of policy). I haven't found a way to hide/suppress source fields in a pivot table so I'm guessing that it is not possible. So I need to somehow get to a dataset that I can use to source my pivot which does not include the confidential data but includes my calculation.
Options I'm considering:Via VBA: Create a copy of Data Set A (including my cost field) as Table C on another sheet and remove the confidential columns from the table. The Pivot Table would be sourced against TAble C. My assumption is that I'd have to be very careful with how I clear out and re-populate table C as not to have to "start over" with my Pivot Table each time it is refreshed. Ideally the pivot design would look just as it did before the user hit the "Refresh Data" button (other than it has the updated data).Order my fields in data set A (with the calculated field) such that I can define a named range that is the source for pivot table. The range would exclude the confidential fields. The Refresh Data macro will just need to resize the named range after the data is refreshed from SQL server. With this option, I just have trouble trusting that excel will never arbitrarily change the field order when I refresh the data connection.Create Data Set C using another query from SQL Server that only has the fields that I want available in the pivot plus a "sumproduct" formula that gets cost from Data Set A. The Refresh Data macro would refresh A then C then the Pivot table. What I don't like about this is having to keep the 2 queries in sync -- there is not a large risk of these queries needing to change, but you never know.
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'
I have a database table with selling prices. I want to get a count of number of customers by sales price range.
For example:
Price Count
$0-$5 #
$5-$10 #
$10-$15 #
Total
My nose says this is what a pivot table should do but there are too many sales prices and the pivot table craps out. How do I get them into ranges as above and then count them?
I've created a PivotTable using VBA that contains hundreds of PivotItems, which would look bad when a PivotChart is made.
I'd like to set the PivotTable to make visible only the first X items (let's say 10). How would I do this in VBA?The macro recorder gives me the name of the PivotItem, but this varies so I'd like to use an index:
VB:
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item1").Visible = False
ActiveSheet.PivotTables("PivotTable4").PivotFields("UWI").PivotItems("Item2").Visible = False
See attached.
Basically, I just want to do a pivot which shows the Name and Number for those names listed in the LIST tab.
The pivot source is the data tab, and the result is in the result tab.
I have the code to create the pivot, but filtering it for those specific names on the LIST tab is where I am getting stuck.
Attached File : Excel VBA Pivot Problem.xlsx
Is there a way to have a Pivot Table show only the Top 10 items based on dollar amount. Data covers a month of daily activity (+/- 250 rows), but i only want the Top 10 items based on Dollar amount. Is this possible?
I know filters can do top 10 but it doesn't consolidate similar items.
I've created a pivot table that is not grouping "like" items. I have verified all fields are numbers using the =isnumber() formula. All items present with the "true" value. Just in case, I tried doing text to columns and refreshing the data but that did not work either. All items are formatted the same. All data fields have values.
View 1 Replies View RelatedI need to deselect all pivot items in a Chart but I receive this error message
Run-time error 438
Object doesn't support this property or method
This is my VBA code
Chart3.PivotLayout.PivotTable.PivotFields ("Year/Month")
For i = 1 To Chart3.PivotLayout.PivotTable.PivotFields.Count
Chart3.PivotLayout.PivotTable.PivotItems(i).Visible = False
Next i
I have a userform, on the user form I have a combo box. when i select an item from the combobox list. I want it to show only that item in the pivot table. here is my code.. Can anyone see where im going wrong? or what i need to ammend to achieve this?
Dim i As Integer
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Description")
For i = 1 To .PivotItems.Count
If i = ComboBox2 Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next
End With
How to select pivot items in a pivot field based on choices from a MULTICOLUMN listbox?
At present, all I know is how to deselect pivot field values
Range("A5").Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("MONTH")
.PivotItems("MAY").Visible = False
Because the pivot table automatically starts with all items selected?
I have made a report in pivot table. My Headings
Sales A Sales B Sales C
Region Name
Location Code
Location Code
Category
Location
Cluster Head
After made the pivot i started the grouping but is ther any limitation for excel for this? It is showing the below message. "Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable, or to page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut. How to over come this and do the grouping. Is this limitation of Excel. I am using Office 2000
I'm running reports in one workbook on different types of spend for 12 business units using 1 worksheet containing the raw data, and 6 other sheets with pivot tables showing different levels of detail.
I've used simple VBA to prepare to standardise the raw data and refresh all the pivot tables with no problem
I need to filter the data to show spend for specific business units. I'd like to do this using a listbox showing the units where I can select the unit(s) to report on, which in turn will only make the units selected visible on each table in the workbookwith no other intervention.
There is another thread on here (showthread.php?t=55041) that shows how to use the list box to control 1 pivot table on one sheet but I can't find anything which shows me how extend this to cover multiple tables over multiple sheets.
I have to check if two items are in column A, and if yes, there values from column B shoulded be summed. I tried different combinations with IF, LOOKUP but didn't go far.
View 2 Replies View RelatedI am trying to use the bellow code to set one pivot item (MyItem) to true and the rest to false... unsuccesfully
Code:
For Each pt In Sheets("Schedule Dashboard").PivotTables(PivotTable1)
If pt.PivotFields("District").PivotItems(MyItem).Visible = False Then
pt.PivotFields("District").PivotItems(MyItem).Visible = True
Else
pt.PivotFields("District").PivotItems.Visible = False
End If
Next pt
I'm trying to write a macro to select the multiple sets of the same data for several PIVOT tables. I've tried Slicers but it seems that this takes up too much processing power and always times out.
My workaround is to do a macro that picks out the said data, however when i do the below, plus another 4-500 lines i get told that there are too many line continuations
Code:
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Postal District].[Postal District].[Postal District]").VisibleItemsList = _
Array("[Postal District].[Postal District].&[AB11]", _
"[Postal District].[Postal District].&[AB12]", _
[Code] ...
What I'm looking to do is express all the postcodes in one line or at least multiple post codes in one go, this is what I've tried:
Code:
"[Postal District].[Postal District].&[AB12].&[AB13]"
and
Code:
"[Postal District].[Postal District].&[AB12,AB13]"
But to no avail.
I copied and modified the pivottable code from http://www.ozgrid.com/VBA/hide-pivot-fields.htm. I am getting a "Run time 13" error on the line I colored purple. I tried removing different "Dim" statements to make it work and I'm having no luck.
Sub PickUpPivotTable()
Dim pt As PivotTable, pi As PivotItem
Dim lType As Long, lHarn As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
Set pt = Worksheets("Monthly Pivot Summary").PivotTables("MonthlyPivotSummary")
strCri = "P/U"
For Each pi In pt.PivotFields("Type").PivotItems
lType = pi..........................
I have an array set up with values I want to look in a Pivot Field for and then pull data back based on that selection. It works great until there is an item in my array that is not listed in the Pivot Field. Then it pulls the data from the last item again, which skews my results. So my questions is, how can I skip to the next item in an array if it is not listed in the Pivot Field? Here is the section of code
ActiveSheet.PivotTables("PivotTable36").PivotFields("Product").CurrentPage _
= arrProductVals(intProduct, 2)
Need code that takes names of people from one excel sheet and selects the names in a pivot table field in another excel sheet. Basically I need to know the code that selects pivot items in a pivot field (say name of the pivot field is 'EmpName' and the pivot items are the names of the employees). As of now I check the name in one excel sheet then manually select the name from the 'Emp Name pivot field in the pivot table. There are many names and manually selkecting one by one is very time consuming hence I am trying to automate this.
View 5 Replies View RelatedI have a Pivot Table on which I am unable to drag the row items (Salesperson Names) to a different position. What am I doing wrong? Attached Sample
View 9 Replies View RelatedI am looking for a way in VB to select and unselect items in the list for filters generated in a pivot table.item list. Ho do I do that?
View 5 Replies View RelatedI have a userform, on the user form I have a combo box. when i select an item from the combobox list. I want it to show only that item in the pivot table.
Code:
Dim i As Integer
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Description")
For i = 1 To .PivotItems.Count
If i = ComboBox2 Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next
End With