Log In
Register
Home
September 18, 2020
Offline
I would like a VBA macro so that if I wanted to see only a specific number (e.g. 100) filtered then have the filter in Pivot Table data must filtered to "100", otherwise filtered to “Blank”. If the 100 is not found in the Data then select “Blank”.
Example
Data
100
101
102
103
104
Blank
October 5, 2010
Offline
Hi Richard,
There's not enough info here for me to understand what you are trying to do.
If you want to filter a pivot table to only show 100 then you can use the PT filters? Why use VBA?
Please supply a workbook with data that can be used to illustrate your situation and desired results.
Regards
Phil
September 18, 2020
Offline
Hello Philip - Please see attached sample. I put some comments into the file.
October 5, 2010
Offline
Hi Richard,
no file attached.
A video showing how to attach a file can be found here https://www.myonlinetraininghu.....and-guides
Regards
Phil
September 18, 2020
Offline
Hello Philip hopefully its attached now.
October 5, 2010
Offline
Thanks Richard. So what you are saying is that if you have a company '200' that isn't in the source data, you want the pivot table to set the filter to 'blank' because '200' isn't in your source?
Where are you getting the value '200' from? You haven't explained the full picture and I can't write any code without knowing details. Where would the code get the values from to use as PT filters?
Regards
Phil
September 18, 2020
Offline
Hello Philip
Yes I want to set the filter in the Pivot Table to 'blank' because '200' isn't in your source data.
The 200 is just an example. Basically wanted to show a data table and have an individual pivot for each company.
Data Tab has the two data fields: Column A = Company Number, Column B = Sales Data.
I then want a few Pivot Tables lets say for Company 100 through 200 in separate pivots. If it finds the match to the company codes that is set/hard coded then use the company code number as the filter. If there is no match to the assigned company code for each pivot then select the blank (e.g. 106 to 200 would be blank for the pivot filter since they are not in the Data set).
Sorry if I'm not explaining everything clearly.
Richard
October 5, 2010
Offline
Hi Richard,
I'm still unsure as to where the values for the filter are coming from, specifically for companies that aren't in your source data.
I can write some code that will look through your source data and change the PT filter for every company it finds, but I don't see how or why it would set the filter to a company that isn't in the source data. Why do you want this to happen? Where would the VBA code get the company values from? If it's from the source data then why would the code try to filter for a company that doesn't exist?
Perhaps all you need is 'Show Report Filter Pages...' ?
Click into one of your pivot tables, then from the Ribbon on the PivotTable Analyze tab (I'm using Office 365) click the drop down arrow beside 'Options' in the PivotTable group (the left-most area of the Ribbon) - see attached image.
Then click on 'Show Report Filter Pages...' and from the pop-up window click OK.
This creates a sheet with a PT for every company in the source data.
Regards
Phil
September 18, 2020
Offline
Hi Matt,
Appreciate all the help and time you have given.
The "Data" can change month to month but I want a specific Pivot to populate only if a certain company code is found, in this case 200.
This is the VBA code that I was using to set the Pivot data to filter on company code 200:
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").ClearAllFilters
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").CurrentPage = _
"200"
Since the Data set didn't contain any company code 200 data the filter defaulted to "ALL". Is there VBA logic to say is company code 200 is not found the pivot would select the Blank instead?
Does this make more sense what I'm asking?
October 5, 2010
Offline
Hi Richard,
You can trap the error that is generated when you try to set the filter to a value that isn't in the source data. When this occurs you then set the filter to (blank)
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").ClearAllFilters
On Error Resume Next
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").CurrentPage = "200"
If Err.Number > 0 Then
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").CurrentPage = "(blank)"
End If
On Error GoTo 0
Regards
Phil
P.S. Matt?
1 Guest(s)
