test sheet pivot tables.xlsx
Hi- I have a problem that needs solving- when i add a product code ( which will be from a data validation list) - I want it to autopopulate the filter sections of the pivot tables-as attached example.
I think this may involve VBA but am not certain
any help please
!
Hi Tradesman,
It is hard to know what you really want here. Have you tried to use the SLICER tool on your pivots? That might be what you want.
Also a Dynamic Named Range of your data and updating the Pivots when stuff is added might also be what you want.
I wrote some code a few years ago that allowed 3 different pivots to show the same filters and that might also be what you want. See that attached:
One test is worth a thousand opinions.
Hi Martin,
bascially if I type ina product code in the cell - it actions the filters in the pivot table - slicers dont work!
Ill google dynamic named range
I was considering a VBA perhaps that when product coe is entered- button next to it action - it then takens that code and uses it in each filter??
this owrks for one pivot table but how do I add the second one- or more!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'B2 or B3 is touched
If Intersect(Target, Range("B2:B3")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to suit your data
Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("product")
NewCat = Worksheets("Sheet1").Range("B2").Value
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Any help guys ??
Sent from my iPhone using Tapatalk
Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
added check for product codes that doesn't exist with helper cell "H2"Please Login or Register to view this content.
If there was a pivot table with 2 filters - 1 being the product - can this ignore non product filters
Sent from my iPhone using Tapatalk
it should
it will look for the pivotfield named "product" to apply filter on only
oh btw
if you want it automatic
put this code in sheet 1 as well
i took out the helper cell and put it into the code itself in this version as wellPlease Login or Register to view this content.
Last edited by humdingaling; 04-22-2015 at 03:50 AM.
Ive amneded it to my larger sheet but I get this occassionally
screensht.JPG
It has updated the pivot tables
Ive amneded it to my larger sheet but I get this occassionally
Attachment 390590
It has updated the pivot tables
Ok - the error is caused when a pivot table doesnt have a result. If there is an error the table filter should default to either nothing or ALL
in excel I would probably use =IfERROR FUNCTION TO DEFAULT TO ZERO
Hi Sorry to bump this up - but if one of the pivot tables doesnt show a result- then the debug screen pops up BUT NOT ALL PIVOT TABLES get updated??
am I missing something
you do realise australia and england are in different time zones.....
anyway you adapted the code to your file but didn't include the file
so i really dont know what you have done
code for clear if not found, you can blank out the msgbox if you dont want it
also if one pivot table shows result and another doesn't it means you aren't using the same data for both pivot tables?Please Login or Register to view this content.
Hi- sorry I didnt realise you were in Oz- ( thanks for the thrashing at cricket - lets hope we can do a little better at the rugby !)
yes there is one pivot table that is based on a different data table- it does refer to a data colum with the same name Partno
it seems to work but stop if one of the tables falls over
we aim to please but everything in circles
would like to see how we fair against England in the football actually
having trouble seeing what is happening on your sheet
are you able to provide sample
pivot tables being what they are...its hard to replicate 100% without the example
Sorry my sheet is 11meg !!! Great help I'll try and figure it out - it still falls over when there's no result from a table
Sent from my iPhone using Tapatalk
This code is your checkPlease Login or Register to view this content.
if the example it covers all the data but if it does it encompass all the data it needs to check in your version of the spreadsheet then you may need another layer of check or several
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks