Is it possible to make cell value equal to currently applied filter?

I have column with data like: 001.1, 002.1, 003.1... (I have 100 rows) I need L1 cell (for example) to be equal to selected filter of that column.

So if I filter 002.1, then L1 = 002.1

How can I reach this please easier than like this?

Sub filtr()
If ActiveWorkbook.SlicerCaches("Průřez_Lokace").SlicerItems("001.1").Selected = True Then
    Range("L1") = "001.1"
        If ActiveWorkbook.SlicerCaches("Průřez_Lokace").SlicerItems("002.1").Selected = True Then
            Range("L1") = "002.1"
        End If
End If
End Sub

Or maybe something like this?

For i = 1 To 120
    If ActiveWorkbook.SlicerCaches("Prùøez_Lokace").SlicerItems(""00" & i & ".1"").Selected = True Then
        Range("L1") = "00" & i & ".1"
    End If
Next i

Or something like this?

For i = 1 To 9
    x = "00" & i & ".1"
    If ActiveWorkbook.SlicerCaches("Prùøez_Lokace").SlicerItems(x).Selected = True Then
        Range("L1") = x
    End If
Next i

Unfortunately first (the most complicated) is working of course, but other two not.


Here is a super simple example:

  • one column
  • one criterium
  • autofilter

enter image description here

If the user sets the AutoFilter to display Robert and runs this macro:

Sub dural()
    Dim rng As Range, r As Range, i As Integer
    Set rng = Range("A:A").Cells.SpecialCells(xlCellTypeVisible)
    i = 0
    For Each r In rng
        If i = 1 Then
            Range("B1").Value = r.Value
            Exit Sub
        End If
        i = i + 1
    Next r
End Sub

Then Robert will be placed in cell B1. This takes advantage of the fact that what is displayed in column A (other than the header) matches the criterium.


Here's a start:https://www.mrexcel.com/forum/excel-questions/591287-start-macro-when-changing-value-slicer.html

Use this sub in your workbook:

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
    'your macro
End Sub

I only played with it a little (sticking in a msgbox to pop up every time a slicer button is clicked), but it should be more elegant in that you don't need to run the VBA every time after you click on a slicer button.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.