Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
Hi, I am new in VBA and I have created a code that gets information
from an access query and set up a pivot table. The worksheet contains multiple pivottables. All pivot tables have at least one page field (product). The worksheet should be copied and then every pivot on the active sheet should be filtered on one product (example: worksheet for milk, worksheet for cheese, worksheet for etc). This for every product (couple op 30-35 products). How can I copy the 'mastersheet' (named ' Master') and filter all pivot tables for a specific Page field product? May be copy worksheet and set cell a1 to a specific value? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
Hi Michel,
You can try the code below. Dim pt As PivotTable ActiveSheet.Select For Each pt In ThisWorkbook.ActiveSheet.PivotTables pt.PageFields("FIELD_NAME").dataRange = "CHOSEN_VALUE" Next You can filter multiple things in this For-Loop. You can also add If Else statements to check for pt.name to apply extra filter to certain pt in the sheet. Note that I didn't have any error checking in here because you didn't ask for, but as long as you are not asking the macro to filter the pt to something that is not on the list then you are fine. Hong Quach " wrote: Hi, I am new in VBA and I have created a code that gets information from an access query and set up a pivot table. The worksheet contains multiple pivottables. All pivot tables have at least one page field (product). The worksheet should be copied and then every pivot on the active sheet should be filtered on one product (example: worksheet for milk, worksheet for cheese, worksheet for etc). This for every product (couple op 30-35 products). How can I copy the 'mastersheet' (named ' Master') and filter all pivot tables for a specific Page field product? May be copy worksheet and set cell a1 to a specific value? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
Indeed, now I have a problem. The pivottables have two different sources. In
on source, not every value is in. Example: for pt1 I can select "Milk" in the pagefield "Product", but pt2, doesn't have and returns "All". I would like that if the error occurs he skips the producing of this pivottable. Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
Hi Michel,
A quick and simple fix to this problem would probably be adding the "On Error Resume Next" statement (ignore error) before the loop begin and "On Error Goto 0" (stop ignoring error). Other method is to adding an inner loop to check each item in the field. Hong Quach "Michel" wrote: Indeed, now I have a problem. The pivottables have two different sources. In on source, not every value is in. Example: for pt1 I can select "Milk" in the pagefield "Product", but pt2, doesn't have and returns "All". I would like that if the error occurs he skips the producing of this pivottable. Is this possible? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
The error resume next - error goto 0 is not solution I can use. He the
chooses the default filter "All" so every value is shown. What I want him to do his quite making the specific pivottable. Is this something which is possible: So how to program a macro which indicates that if a cell value is not in the page field list, he should not create the pivot, and if it is in the pagefield list, he should create it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
Hi,
I am not quite understand your situtation described below. My guess is that you want a macro that will do the following: 1. Set all pt to filter on "All" 2. If all pt contains a "CHOOSEN_VALUE" in the pt.pagefields("CHOOSEN_FIELD") Then a. Change all pt to filter on the "CHOOSEN_VALUE" Else a. Change all pt back to filter on "All" 3. End If If what describe here would work for you, then you can make a label at the bottom of the code to change all pt to filter on "All", then use the "On Error Goto <Lable" to reset all pt back to filter on "All". Private Sub FilterPTOnAll() ' Code to loop through each pt and set the filter to All End Sub Sub YourMacro() FilterPTOnAll On Error Goto ResetPT ' Code to loop through each pt and set the filter to the "CHOOSEN_VALUE" Exit Sub ResetPT: FilterPTOnAll End Sub Hong Quach "Michel" wrote: The error resume next - error goto 0 is not solution I can use. He the chooses the default filter "All" so every value is shown. What I want him to do his quite making the specific pivottable. Is this something which is possible: So how to program a macro which indicates that if a cell value is not in the page field list, he should not create the pivot, and if it is in the pagefield list, he should create it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Page field filter based on cell value
Thanks will try this!
"Hong Quach" wrote: Hi, I am not quite understand your situtation described below. My guess is that you want a macro that will do the following: 1. Set all pt to filter on "All" 2. If all pt contains a "CHOOSEN_VALUE" in the pt.pagefields("CHOOSEN_FIELD") Then a. Change all pt to filter on the "CHOOSEN_VALUE" Else a. Change all pt back to filter on "All" 3. End If If what describe here would work for you, then you can make a label at the bottom of the code to change all pt to filter on "All", then use the "On Error Goto <Lable" to reset all pt back to filter on "All". Private Sub FilterPTOnAll() ' Code to loop through each pt and set the filter to All End Sub Sub YourMacro() FilterPTOnAll On Error Goto ResetPT ' Code to loop through each pt and set the filter to the "CHOOSEN_VALUE" Exit Sub ResetPT: FilterPTOnAll End Sub Hong Quach "Michel" wrote: The error resume next - error goto 0 is not solution I can use. He the chooses the default filter "All" so every value is shown. What I want him to do his quite making the specific pivottable. Is this something which is possible: So how to program a macro which indicates that if a cell value is not in the page field list, he should not create the pivot, and if it is in the pagefield list, he should create it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Page Field value from cell in another worksheet | Excel Discussion (Misc queries) | |||
Change a pivot field based on another pivot field | Excel Programming | |||
Update Pivot page field from a cell entry | Excel Programming | |||
Excel Pivot Table link page field to spreadsheet cell | Excel Discussion (Misc queries) | |||
Pivot Table Page field link to cell | Excel Worksheet Functions |