Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Apply Autofilter on multiple sheets (Excel 2002)

    Hi

    I would like to know if it is possible to apply autofilter on multiple sheets, When I select autofilter on the sheet All I would like it be applied to sheets 2003-2004-2005. to give comparisons.

    Please see attached Workbook
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Apply Autofilter on multiple sheets (Excel 2002)

    There is no event associated with filtering a range. Microsoft provides an example of how to simulate such an event, but it is very complicated. See Creating Smart Tags And Event-Code For AutoFilter Results (Microsoft Excel 2002 Technical Articles). A side-effect of using the example would be that you won't be able to use undo any more - a too high price in my opinion.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Apply Autofilter on multiple sheets (Excel 2002)

    Hi Hans

    A bit too complicated for me, as there is only four sheets, I Will stick to manual.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Apply Autofilter on multiple sheets (Excel 2002)

    Another option would be to use a combobox to select the items in the list. This would trigger an event and then the code would run to apply that selection to all the filters on all the sheets

    Since the other sheets are "duplicates" (ie subsets of the entire dataset) I presume you want them to calculate the portion of th filtered for each year. Instead of working up code to filter multiple sheets, why not create a function to calculate the subtotal based on a criteria? something of a "SubtotalIF" that works with a range and tests for "visible" .

    One approach would be to loop thru the range, and place the visible numbers (that match the year) into an array, then calculate the appropriate function on that array of values.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Apply Autofilter on multiple sheets (Excel 2002)

    Hi Steve

    Thanks for the reply, but I am afraid I do not understand the technicalities of what you say, so I would never be able to create it.

    But Thanks anyway.

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Apply Autofilter on multiple sheets (Excel 2002)

    Add this function into a module in the workbook.
    <pre>Option Explicit
    Function SubtotalIf(iNum As Integer, rSubtotal As Range, _
    rIf As Range, vIf As Variant)

    Dim AWF As WorksheetFunction
    Dim vArray()
    Dim lCount As Long
    Dim x As Long
    Dim y As Long
    If iNum < 1 Or iNum > 11 Then
    SubtotalIf = CVErr(xlErrValue)
    Exit Function
    End If
    Set AWF = Application.WorksheetFunction
    lCount = rSubtotal.Count
    ReDim vArray(1 To lCount)
    y = 0
    For x = 1 To lCount
    With rSubtotal.Cells(x)
    If Not .EntireRow.Hidden Then
    If rIf.Cells(x).Value = vIf Then
    y = y + 1
    vArray(y) = .Value
    End If
    End If
    End With
    Next
    ReDim Preserve vArray(1 To y)
    With AWF
    Select Case iNum
    Case 1
    SubtotalIf = .Average(vArray)
    Case 2
    SubtotalIf = .Count(vArray)
    Case 3
    SubtotalIf = .CountA(vArray)
    Case 4
    SubtotalIf = .Max(vArray)
    Case 5
    SubtotalIf = .Min(vArray)
    Case 6
    SubtotalIf = .Product(vArray)
    Case 7
    SubtotalIf = .StDev(vArray)
    Case 8
    SubtotalIf = .StDevP(vArray)
    Case 9
    SubtotalIf = .Sum(vArray)
    Case 10
    SubtotalIf = .Var(vArray)
    Case 11
    SubtotalIf = .VarP(vArray)
    End Select
    End With
    Set AWF = Nothing
    End Function</pre>


    In D2 enter:
    <pre>=SUBTOTALif(9,E$8:E$135,$D$8:$D$135,$B2)</pre>


    Copy D2 to D34

    In F2 enter:
    <pre>=SUBTOTALif(9,F$8:F$135,$D$8:$D$135,$B2)</pre>


    Copy F2 to F3:F4

    Now you don't need the individual sheets to get the subtotals. This ones looks at visible cells and adds the Year as a criteria.

    [Note it is not like the SUMIF and COUNTIF, it only looks for exactly equal]
    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Apply Autofilter on multiple sheets (Excel 2002)

    Hi Steve

    Sorry so long in answering, the function is perfect for my needs.

    Thank so much.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •