"Very Beginner" VBA Filter Lock + Set Filter to Cell Values

[HansVWannaBe]

Hello Loungers,

First, I have very, very, very limited VBA experience. I tried to search the forums for these answers but couldn't figure out where to paste the various codes. I apologize if either of these questions has already been answered - I couldn't apply the answers I found, and I appreciate anyone's willingness to help me out here with VBA baby steps...

I have a pivot table that is already set up but that I would like to do two things with:

1) I would like to set the filter on field "LegType" to "stock transport" permanently. This is because, when we are manipulating data in the table, we sometimes filter individual fields and then select the "clear all" filters function, which removes an important filter. If anyone has a different suggestion, like maybe how to limit the way the data pull from the raw data in the first place to "stock transport" only, I am open to this too.

(If you provide VBA code, please let me know where to paste it - in the sheet code itself vs. create a "module"??)

The worksheet name is "Freight to Terminals". The pivot table name is "FreightToTerminal3MRoll". I want the filter to stay even when I right-click and "refresh" the pivot table.

- and -

2) I would also like this same pivot table to dynamically change based on the month (and year) selected on a different tab. For example, the user selects the month to analyze in a separate worksheet "1) Select Month". I then have a lookup table that attributes the month and year to a certain numeral designator. The numeral designator for September 2016 = 21. I want the pivot table to filter for only the selected month and two months prior. The pivot field "MonthDesignator" has the available numerals to select from. When the user selects "September 2016" on the "1) Select Month" tab, I want the pivot table "FreightToTerminal3MRoll" on the "Freight to Terminals" tab to filter the "MonthDesignator" field to include only months 19, 20, and 21, which are displayed on worksheet "Rolling Month Calcs" in cells D11, E11, and F11. Again, these designators change whenever the user changes the month selected on the "1) Select Month" tab, so I would like the pivot table to refresh any time that happens.

Same thing for this one - if you are kind enough to help me with some VBA code, please let me know where to paste it, as I am a novice.

In the meantime, I will continue my online VBA tutorials, but they are so generic, I am learning quite slowly.

Thank you for any help you can provide!

Jac

[HansV]

1) You can drag the LegType field to the Filters area, then select 'stock transport' from the filter dropdown.
The user can change (or remove) this filter, but Excel itself will leave it as is.

2) Is there a cell that will contain the 'numeral designator' of the selected month/year. I.e. that cell will contain 21 after the user has selected 'September 2016'. If so, which cell is that?

[HansVWannaBe]

Hi Hans,

Thank you for getting back to me so quickly!

For the first one, this is what I have now, the problem is that when we play with the pivot table, we end up filtering a lot by other values, and when the user clicks the "clear filters" button, it clears my main filter. I want the main filter to stay on.

For the second one, yes, the numerals 19, 20, and 21 are in cells D11, E11, and F11 on worksheet "Rolling Month Calcs".

Thanks again!

Jac

[HansV]

Right-click the sheet tab of the sheet.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module that appears:

Code: Select all

Private Sub Worksheet_Activate()
    Dim pvt As PivotTable
    Dim pvf As PivotField
    Dim pvi As PivotItem
    Dim rng As Range

    Set pvt = Me.PivotTables("FreightToTerminal3MRoll")

    Set pvf = pvt.PivotFields("LegType")

    For Each pvi In pvf.PivotItems
        pvi.Visible = True
    Next pvi

    For Each pvi In pvf.PivotItems
        If pvi.Name <> "stock transport" Then
            pvi.Visible = False
        End If
    Next pvi

    Set pvf = pvt.PivotFields("MonthDesignator")

    For Each pvi In pvf.PivotItems
        pvi.Visible = True
    Next pvi

    Set rng = Worksheets("Rolling Month Calcs").Range("D11:F11")

    For Each pvi In pvf.PivotItems
        If rng.Find(What:=pvi.Name, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            pvi.Visible = False
        End If
    Next pvi
End Sub

Make sure that you save the workbook in a macro-enabled format (.xlsm or .xlsb, NOT .xlsx).

[HansVWannaBe]

That. Is. So. Cool!

Thank you. Ok, I am going to play with it a bit to get it to apply to a few other tables and also to set additional filters based on your code. Thank you again so very much!

Jac

[HansVWannaBe]

Ok! I got it to work for all three of my tables. One last question on this one - I have the event firing when I change the value in cell B4 on the same sheet. Really, I want the event to fire when cell B4 on a DIFFERENT sheet changes. I tried just linking the active sheet's B4 to the other sheet, but that did not work. I have also tried several variations to no avail to reference the other cell. Any ideas? Thanks again - I am slowly picking a wee bit of this up!

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
Dim pvt As PivotTable
    Dim pvf As PivotField
    Dim pvi As PivotItem
    Dim rng As Range

    Set pvt = Me.PivotTables("FreightToTerminal3MRoll")

    Set pvf = pvt.PivotFields("LegType")

    For Each pvi In pvf.PivotItems
        pvi.Visible = True
    Next pvi

    For Each pvi In pvf.PivotItems
        If pvi.Name <> "stock transport" Then
            pvi.Visible = False
        End If
    Next pvi

    Set pvf = pvt.PivotFields("MonthDesignator")

    For Each pvi In pvf.PivotItems
        pvi.Visible = True
    Next pvi

    Set rng = Worksheets("Rolling Month Calcs").Range("D11:F11")

    For Each pvi In pvf.PivotItems
        If rng.Find(What:=pvi.Name, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            pvi.Visible = False
        End If
    Next pvi
    
Dim pvt2 As PivotTable
    Dim pvf2 As PivotField
    Dim pvi2 As PivotItem
    Dim rng2 As Range

    Set pvt2 = Me.PivotTables("FreightToTerminal6MRoll")

    Set pvf2 = pvt2.PivotFields("LegType")

    For Each pvi2 In pvf2.PivotItems
        pvi2.Visible = True
    Next pvi2

    For Each pvi2 In pvf2.PivotItems
        If pvi2.Name <> "stock transport" Then
            pvi2.Visible = False
        End If
    Next pvi2

    Set pvf2 = pvt2.PivotFields("MonthDesignator")

    For Each pvi2 In pvf2.PivotItems
        pvi2.Visible = True
    Next pvi2

    Set rng2 = Worksheets("Rolling Month Calcs").Range("D12:I12")

    For Each pvi2 In pvf2.PivotItems
        If rng2.Find(What:=pvi2.Name, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            pvi2.Visible = False
        End If
    Next pvi2
    
Dim pvt3 As PivotTable
    Dim pvf3 As PivotField
    Dim pvi3 As PivotItem
    Dim rng3 As Range

    Set pvt3 = Me.PivotTables("FreightToTerminal12MRoll")

    Set pvf3 = pvt3.PivotFields("LegType")

    For Each pvi3 In pvf3.PivotItems
        pvi3.Visible = True
    Next pvi3

    For Each pvi3 In pvf3.PivotItems
        If pvi3.Name <> "stock transport" Then
            pvi3.Visible = False
        End If
    Next pvi3

    Set pvf3 = pvt3.PivotFields("MonthDesignator")

    For Each pvi3 In pvf3.PivotItems
        pvi3.Visible = True
    Next pvi3

    Set rng3 = Worksheets("Rolling Month Calcs").Range("D13:O13")

    For Each pvi3 In pvf3.PivotItems
        If rng3.Find(What:=pvi3.Name, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            pvi3.Visible = False
        End If
    Next pvi3
End If
End Sub

[HansV]

The Worksheet_Change event does not "fire" if the value of a formula changes. Instead, you'd have to use the Worksheet_Change event of the worksheet that contains the cell that you change manually.

An alternative would be to use the Worksheet_Calculate event, but that causes a lot of overhead. This event occurs whenever a formula on the sheet is recalculated, there is no way to limit it to a specific cell or range (the Worksheet_Calculate procedure doesn't have a Target argument).

[HansVWannaBe]

Thank you, Hans. Do you know how to reference the other worksheet to get the Worksheet_Change event to work from my code above? If not, I will work around.

Thank you again for all of your help!!

Jac

[HansV]

You should place the code that you posted in the worksheet module of the worksheet that contains the cell that you modify manually, not in the module of the worksheet that contains the pivot tables.

You must change all occurrences of Me.PivotTables(...) to Worksheets("Freight to Terminals").PivotTables(...) because Me refers to the worksheet that contains the code, which is now different from the worksheet that contains the pivot tables.

[HansVWannaBe]

Thank you very much. I am rolling along :)

[HansVWannaBe]

It works!!! Thanks again.