Hide/Show Pivot Table Fields
Oct 10, 2007
Im using the following VBA code, but the on error resume does nothing, excel throws out the error 1004 "Unable to get the pivotfields property of the pivottable class" (i know why the error is caused and how to fix it but my error trapping wont work). Is there some option which i have turned off which prevents me from trapping errors?
Dim ws As Worksheet
Set ws = Excel.Sheets("Sheet1")
On Error Goto err:
If ws. Range("IV1").Value = "dog" Then
ws.PivotTables("PivotTable1").PivotFields("Price Euros").Orientation = xlHidden
Else
ws.PivotTables("PivotTable1").PivotFields("Price Dollars").Orientation = xlHidden
End If
err:
msgbox "invalid"
View 5 Replies
ADVERTISEMENT
May 6, 2007
I am creating a pivot table from region, which some of the rows are blank.
Is there any possibility that the pivot table not show the blank in its dropdown combobox?
View 4 Replies
View Related
Feb 24, 2008
I'm just stuck on a final thing......I would like the pivot table to collapse based on the outcome of a formula. For instance I've got a project that has A, B, C, D as component parts, these parts all have a percentage work completed. If let's say A is a 100% complete I would like to automatically collapse A, so it will just show the summary for A. Obviously this can be done by hand, but since there is quite a lot of data I would like to automate the proces.
View 8 Replies
View Related
Apr 5, 2008
I work at a trading firm and use pivot tables to report on the success of traders on a daily basis. I add daily trading data to a raw data table that powers a set of reports. In one report I want to view MTD stats for a filtered group of 10 traders. The issue is that if I add a set of daily data that includes a new trader name, it will automatically be pre-checked and added to this report (and this happens daily). The only solution I came up with is to add another column in the raw data table that would allow me to group these traders and then use a page filter to include only them. This will work but I'd rather avoid adding columns to an already unruly data table (and would like flexibility to periodically define and track an arbitrary set of traders).
View 4 Replies
View Related
Dec 12, 2007
I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:
Sub FilterDates()
Application. ScreenUpdating = False
Sheets("PnL").Select
Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated
View 9 Replies
View Related
Feb 22, 2008
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
View 4 Replies
View Related
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
May 15, 2013
How to hide the Pivot Table pagefields . Because I have so many filters on my pivot table to get it where I want it to be, I chose to use some PageFields. Trouble with these, is they look awful on my report which will be issued in PDF format so not an interactive field at all.
When I use the PF.Orientation =xlHidden on a page field, it affects the accuracy of the report
If I use this on a rowfield, no problem.
Is there a VBA way to hide pagefields?
Code:
Set PF = pt.PivotFields("Hours Code Effective")
PF.Orientation = xlPageField
With pt.PivotFields("Hours Code Effective")
.PivotItems("Yes").Visible = True
.PivotItems("No").Visible = False
.PivotItems("(blank)").Visible = False
End With
' Set PF = pt.PivotFields("Hours Code Effective")
' PF.Orientation = xlHidden
View 6 Replies
View Related
Jun 17, 2008
I have pivot tables with one row field and two or three data fields. I want to be able to quickly hide rows that do not have entries in *all* data fields.
I thought of auto- filtering my data source, but don't think that would be practical as I want to have several tables powering off of the same source data (with many columns), and also the data source is auto-filled with API... Is there a way to traverse the rowfields with vba and hide them if there isn't an entry for every data field? Or a better way than both of those?
View 4 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
May 2, 2007
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;
Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
ghost.Delete
Next ghost
Next Count
pt.ManualUpdate = False
End Sub
My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............
View 2 Replies
View Related
Jul 20, 2009
I'm trying to loop through all pivot fields in a table and change the format to be 0 decimals and comma seperated.
Here's my
View 3 Replies
View Related
Jun 22, 2006
I 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 Related
Jan 3, 2014
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
View 2 Replies
View Related
Nov 28, 2012
My data has a field for "Donor Type." There are about 2 dozen types of and about half of them start with the letters CF. When I do a pivot table, instead of getting 2 dozen rows of data I'd like all the CF donors to be in the same row. Is there a way to do that without changing my data? Some sort of filter or grouping within the pivot table?
View 1 Replies
View Related
Jun 7, 2013
I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.
Day
Count of Posts
Sum of Impressions
Impressions Per Post
Monday
52
1,881,468
[Code] ........
View 1 Replies
View Related
Feb 9, 2008
Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.
I have a very simple excel 2007 document that has one column with multiple rows;
blue
Blue
green
red
red
green
Green
blue
red
Blue
blue
Bleue
Rouge
red
Bleue
I would like the easiest way to count the total of each and sort in highest first,
e.g.
red 4
blue 3
Blue 2
green 2
View 9 Replies
View Related
Feb 1, 2010
I have written code to add datafiels to pivot table but I need to remove the ones that are their prior to adding.
Does anyone have a technique using vba to do this.
View 9 Replies
View Related
Aug 1, 2012
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
View 5 Replies
View Related
Jun 17, 2009
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
View 2 Replies
View Related
Aug 28, 2008
I have pivot tables in diff pages
im trying to make a button to reset all the fields in all the pivot tables..
i tried recording a macro to do this but it only works for page fields..
it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....
this isthe code it gave:
View 13 Replies
View Related
Jan 21, 2012
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date
-------------------------------------------------------------
John , 100 , $150 , 2011-04-01 17:07:50.0
John , 100 , $250 , 2011-04-01 17:07:50.0
Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase
-----------------------------------------------------------
John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
View 2 Replies
View Related
Mar 29, 2012
I have a pivot table listing different company names in the first column under 'row labels' and there are calculated fields, a count and an average in columns B and C respectively. Is it possible to insert another field in column D that calculates the difference between values in column B and C (even though they are calculated fields themselves)?
View 3 Replies
View Related
May 23, 2007
Worksheet A contains two columns, that maps individual operations to their unitary cost:
COLUMN 1 = a list of operations
COLUMN 2 = the cost associated with each operation.
I can do a vlookup on this worksheet to retrieve the cost of each individual operation.
On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.
operation 1 was performed 5 times
operation 2 was performed 7 times
operation 3 was performed 4 times
I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be
= operation * vlookup("name of operation","range for lookup table",2,0)
... unfortunately, "references, names and arrays are not supported in pivottable formulas".
View 9 Replies
View Related
Sep 26, 2007
I have a pivot table which draws data automatically from a database
What I would like it for the customer field of the pivot table to only equal the customers which are present in another worksheet (Column A:A)
View 9 Replies
View Related
Oct 17, 2006
Is there any code or way to generate the compete list of fields that are selectable from the " PivotTable Field list"?
I have various cubes I need to check and compile the list of fields for each.
View 7 Replies
View Related
Aug 29, 2007
I want to print out, for a range of 'Page Fields', from a Pivot table automatically.
The Macro must be able to:
Automatically go through and select the 'Page Fields' in the pivot table
Select the print area range automatically, which will change each time the pivot is refreshed/reselected.
Set the print area to fit the page for each category selected in the pivot table
Finally print.
Can't find code for this anywhere, but i must admit got fed up with looking
View 3 Replies
View Related
Feb 12, 2014
I have a Pivot table that pulls the Avg of two fields for two months, see example below.
Avg Gross $ Avg Net $
Jan 2014 20 10
Feb 2014 30 20
sample 1.png
See sample attached.
The Avg Gross and Net is shown by going into the values and selecting "Summarized value by -> Average".
On the right side of this Pivot, what I wanted to do is to show a Avg Gross to Net $ in this pivot. So the formula should take "Avg Gross $" - "Avg Net $" = Avg Gross to Net $.
I am having trouble calculting this new field in the pivot table using a calculated field because the Calculated field pulls the variables from the existing field list and there isn't a field called "Avg Gross/Net"....I need to find a way to calculate the Avg Gross to Net into the Pivot table so I can pull a pivot graph out of it.
View 3 Replies
View Related
Aug 16, 2012
I have been having issues with some excel fields not showing up in my pivot table pull downs.
For example I entered the date 6/8/97 into a field in my database and after I hit refresh on my pivot table that specific date doesn't show up in the Date field list pull down. It is like the pivot table doesn't even see that date and/or it is recognizing it as another date. I have this same issue w/ other field lists (eg. last names).
View 3 Replies
View Related
Nov 11, 2013
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.
View 4 Replies
View Related
