Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Autofilters
Hi,
I have an table which is autofiltered on a worksheet. I've just had to add another table on another worksheet and i want this one to filter when i change the filter on the 1st sheet. Is this possible in VBA? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Autofilters
Hi
This solution uses a UDF created by Tom Ogilvy to find the criteria that have been set on a Sheet's Autofilter I used Sheet2 and Sheet3. Adjust to suit your needs. Sub Filtersame() Dim setfilter As String setfilter = ShowFilter(Sheets("Sheet2").Range("A1")) Sheets("Sheet3").Select Selection.AutoFilter Field:=1, Criteria1:=setfilter End Sub 'Here is a user defined function that will display the criteria in a cell: 'created by Tom Ogilvy Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- Regards Roger Govier "N1KO" wrote in message ... Hi, I have an table which is autofiltered on a worksheet. I've just had to add another table on another worksheet and i want this one to filter when i change the filter on the 1st sheet. Is this possible in VBA? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilters | Excel Worksheet Functions | |||
How to set up AutoFilters? | Excel Worksheet Functions | |||
autofilters | Excel Worksheet Functions | |||
VBA and Autofilters | Excel Programming | |||
Userforms and autofilters - Autofilters don't seen to work with userform | Excel Programming |