Conditionally Hide Pivot Data Fields
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
ADVERTISEMENT
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
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
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
Jan 26, 2013
I'm currently working on a maintenance task sheet for a couple of generators. I have successfully created button macros to input dates. I would like a macro to copy all the cells that are RED (due to a condition rule) plus their associated fields to the left from worksheet "Schedule" to worksheet "Tasks Due". [URL]
Here's the pre-programming flow, I just don't know the code to do it:
Scan worksheet "Schedule" for red cells in following Range
H8:I16,H18:I22,H24:I35,H37:I41,H43:I49,H51:I59,H61:I72,H74:I75,H77:I84,H86:I89,H91:H100
If cell = red Then Copy Row A:I
Insert Row into sheet ("Tasks Due")
If red cell is in worksheet "Schedule" H8:I16 then insert copied cells in worksheet "Tasks Due" one below cell = Fuel System
If red cell is in worksheet "Schedule" H18:I22 then insert copied cells in worksheet "Tasks Due" one below cell = Lubrication System
[Code] .....
If my uploaded workbook doesn't work I've linked screenshots of my document.
Top of "Schedule" worksheet: [URL] .....
Middle of "Schedule" worksheet: [URL] .....
Bottom of "Schedule" worksheet: [URL] .....
"Tasks Due" worksheet: [URL] .....
View 4 Replies
View Related
Jan 11, 2013
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
View 1 Replies
View Related
Dec 31, 2013
I created a nice excel file with some cohort analysis regarding the number of months customers of different cohorts stay with a company. Basically I do a little manipulation on the raw sign-up data sheet and then run a pivot on it.
I want to see the cohorts in two ways:
1. number of customers.
2. $ from customers.
I have the first 1 and I want to get the second one without going through the same procedure again (i.e. manipulating the raw data). In order to get the $ terms, all I need is to multiply a $ column in each row in the columns that indicate the number of months that passed since a customer joined (see the attached example file please). When I try to do it with calculated fields in the pivot table, Excel multiplies the total number of customers with total $ from them all put together, which is of course wrong.
Any way to achieve what I want without having to created another data sheet just for $ terms?
I believe the attached excel file: dummy_for_cohort.xlsx
View 6 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
Feb 12, 2014
STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:
When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.
UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.
View 2 Replies
View Related
Oct 12, 2007
I am currently using the following code to hide each row when there is no data between a certain column range. I need to add to this code so that it will also hide each column when there is no date between the row range.
Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
ActiveWindow.DisplayZeros = False
Application. ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Jul 28, 2009
I have about a dozen tabs, all of which have vlookups in them and calculate based on what a user pastes into Sheet 1. I need a macro that hides all tabs that have no data, essentially tabs where A1 = ""
However, it also needs to unhide tabs as soon as there is data. So if the user pastes new data into Sheet 1 the vlookups, on the other sheets, still need to run (even if they are hidden) and then check against the macro to determine whether to be hidden or unhidden.
View 3 Replies
View Related
Aug 4, 2007
The below sheet sometimes returns blank (“”) cells in columns C:F. I’m looking for a code that hides the rows 113: to 117 when column C:F (all of them) are empty (“”) for that row. In the above example rows 115:117 should be hidden....
View 9 Replies
View Related
Sep 29, 2006
I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list).
For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
View 4 Replies
View Related
Sep 4, 2007
Is it possible to hide some of the number labels on an axis; e.g. in on the chart below to hide the X-axis label 96? Or only start numbering from 97 while the chart displays from 96. Auto Merged Post;Well I have a solution to hiding the first value on the excel X axis values labels just using a custom number format;
[Black][>96]General;
but is there anyway of formating more than a couple of the number labels individually?
View 2 Replies
View Related
Nov 22, 2007
I am trying to hide rows based on the contents of column BS. It the cell is empty (not just zero value, but completely empty of all formulae), then I want to leave it unhidden, then if the cell value is less than the value in cell E2 I want to hide the row. This code doesnt seem to like the first if...then next i
Sub hiderows()
Dim i As Long
Set v = cell.Value("$E$2")
' Turn off screen refresh for speed
Application. ScreenUpdating = False
' Get last row Col BS
For i = Range("BS2000").End(xlUp).Row To 15 Step -1
If IsEmpty(Cells(i)) Then Next i
ElseIf Cells.Value(i) < v Then Rows(i).Hidden = True
End If
Next i
' Turn on screen refresh
Application.ScreenUpdating = True
End Sub
View 4 Replies
View Related
Jun 5, 2013
I'm quite new to VBA, but I am attempting to get a Forms ComboBox to appear or disappear based on whether a certain cell (P7) reads YES or NO. P7 in turn updates in turn based on a user-selected value. As of now, the ComboBox only appears or disppears if I go back in and out of the formula I entered into P7. Basically, I want my ComboBox to dynamically update based on the value in P7. That may sound a little muddled, so here is my code for the ComboBox:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P7")) Is Nothing Then
If UCase(Target) = "YES" Then
Me.Shapes.Range("Drop Down 30").Visible = msoTrue
Else
Me.Shapes.Range("Drop Down 30").Visible = msoFalse
End If
End If
End Sub
View 3 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
Jan 22, 2009
After scouring the entire OzGrid, I have found the following two macros that partly satisfy what I want to achieve. BTW, following two macros are executed on Sheet2 and I want to achieve the following:
On Worksheet_Activate, show columns that match the criteria value in Sheet1.D4 (Sheet1.D4 contains a dropdown list with about 6 text values). In addition to that, if Sheet1.D4 value is blank then show all columns. Right now, it successfully shows columns that match the D4 value but does not work if that D4 value is left blank, instead it hides all columns in range (C:CV). So in short, to the first macro, I need to add the criteria if D4 is blank, then keep the sheet intact and don’t hide anything, just show all columns.Once on that sheet after the Worksheet_Activate event has occurred, with the second macro “ToggleColumnsVisibility” following things need to happen. BTW, currently this macro is assigned to a button (from Forms Toolbar).If cell D4 has a value, then the button’s caption should read “Show All” and when that button is pressed, it should show all columns in the range (C:CV), and the button's caption needs to change to “Show Selected”. In “Show Selected” mode, when that button is pressed, it needs to show all the columns that match the D4 value.If cell D4 is left blank, then the button's caption should read "No Action" and when the button is pressed, it should not do anything but just show all columns.
As you will notice in the header, my experience with Excel is very limited, so detailed instructions will be greatly appreciated. I am also hoping that proposed solution uses some of the efficient/effective ways of using loops such as described below (example taken from this website from this link http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm)
Sub TrueOrFalseFaster()
Dim bYesNo As Boolean
Dim i As Integer
bYesNo = (i = 5)
MsgBox bYesNo
End Sub
Sub ToggleTrueOrFalseFaster()
Dim bYesNo As Boolean
bYesNo = Not bYesNo
MsgBox bYesNo
End Sub .........
View 9 Replies
View Related
May 19, 2008
I have used a pivot table with help from members here for a rota.
Now i am wondering if i can add a column to the original data which is hidden to times the number of hours worked by the hourly rate which is in another sheet.
The pivot table will be in an admin sheet with protected access so employees cant see it.
View 9 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
Oct 8, 2008
I have a pivot which under a Main product, there are a few sub products. I have act, budget, forecast etc figures but mainly want to only show the Total Act and not the rest, see the highlighted brown columns which I do not want to show in the pivot.
Then I want to group the clients, salesperson together so that the details can be hidden.
This is a small example. I have a large pivot table with lots of teams and salespeople and clients and a few main products with a few sub products within it. Grouping it manually is going to be very difficult and would appreciate if there is some way to automate it either via pivot capability or VBA.
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
Aug 7, 2006
The code to make all pivot fields in the "PivotTable1" equal exactly as chosen to the ones in "PivotTable2"? Cheers.
View 9 Replies
View Related
Feb 1, 2010
it's possible to create new fields (variables/columns) on the fly (without adding them to the original data table).
For this example, assume I have a column for:
job code estimated hours
job code actual hours
total estimated hours
total actual hours
Excel has made it easy to run averages or sums for any of those columns, but what if I want to see what the average proportion for job code estimated hours/ total estimated hours? Is there an easy way to do it with pivot tables without adding a column to my database? I'm working in Excel 2007, my data table size is approximately 100 columns by 200 rows.
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
Mar 14, 2008
I am finishing up a macro that manipulates a pivot table and creates a file for us to run. But we all keep this pivot table up a lot, and usually keep a lot of the data filtererd in it.
Is there a way to set all of fields in a pivot table back to their default values like when you first open up the workbook?
View 9 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
