# Cell values based on applied filter

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"
Else
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

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.

Use this sub in your workbook:

``````Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)