Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Apply Auto Filter in Sheet 1 to all other sheets

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Apply Auto Filter in Sheet 1 to all other sheets

    Hi all

    I have a workbook with multiple sheets.

    In Sheet 1 there is only a list of Client Managers, which I want to filter on. All the other sheets with all the data have Client Managers in column K. The number of columns in the various sheets range up to a max of AZ.

    So, if I filter on "Charlie Brown" in Sheet 1, I would like all the other sheets to be automatically filtered on Charlie Brown.

    From what I can tell the only way to do this is via a VBA macro, therefore, I gather, the filter is applied in Sheet 1, a button needs to then be clicked to apply the filter to the other sheets.

    I am a complete beginner with regards to VBA though. And the other scripts I have found online just don't work - I don't know how to modify them to fit my needs.

    I would appreciate any assistance please! Including, for example, if all the data sheets have the Client Managers in column K, does the filtered list in Sheet 1 also need to be in column K?
    Last edited by BiggusDoggus; Jun 26th, 2018 at 08:07 PM.

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    Ok, I have tried this:

    Sub apply_autofilter_across_worksheets()
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
    xWs.Range("K").AutoFilter 1, CLng(Sheets("Sheet2").Range("C1").Value)
    Next
    End Sub


    Instead of using a filter in Sheet2, I have used a Data Validation field in C1 using a list, so creating a drop down.

    When I run the macro:

    • Positive - no error!
    • Negative - nothing happens


    I'm not sure what this does: xWs.Range("K") - the original script had a number after the column letter, but no matter what number I put after it, it makes no difference.

    I also simply typed a Client Manager name into C1, with no impact. So clearly it's just all busted.

    Help please!

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,821
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    Are you saying when you enter a value in Range("C1") you want to filter all other sheets in your workbook on this name entered into range("C1") on Sheet 1 ??

    And in what column on all these other sheets do you have this name?

    So on Sheet(1) if you enter George in Range(C1) you want to filter all other sheets on the name "George"

    In what column on these other sheets will we find this name George ??
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    Quote Originally Posted by My Aswer Is This View Post
    Are you saying when you enter a value in Range("C1") you want to filter all other sheets in your workbook on this name entered into range("C1") on Sheet 1 ??

    And in what column on all these other sheets do you have this name?

    So on Sheet(1) if you enter George in Range(C1) you want to filter all other sheets on the name "George"

    In what column on these other sheets will we find this name George ??
    That's correct (although the name will be selected from a drop-down, rather than entered, but that hopefully is just semantics).
    Column K in the other worksheets is where the Client Manager names are.

    Cheers!

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,821
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    Try this:

    Run this script from Sheet 1
    This script must be assigned to a Button or run some other way.
    You did not say you wanted it run automatically.
    It looks in the first sheet in your workbook for a value in Range("C1")
    Code:
    Sub Filter_Me_Please()
    'Modified 6/26/18 10:35 PM EDT
    Application.ScreenUpdating = False
    Dim Lastrow As Long
    Dim c As Long
    Dim s As String
    c = "11" ' Column Number Modify this to your need
    s = Sheets(1).Range("C1").Value
        For i = 2 To Sheets.Count
            Lastrow = Sheets(i).Cells(Rows.Count, c).End(xlUp).Row
            With Sheets(i).Cells(1, c).Resize(Lastrow)
                .AutoFilter 1, s
                counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
                If counter > 1 Then
                Else
                    MsgBox "The Value " & s & "  Not found on Sheet  " & Sheets(i).Name
                    .AutoFilter
                End If
            End With
    Next
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  6. #6
    Board Regular
    Join Date
    Jul 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    Quote Originally Posted by My Aswer Is This View Post
    Try this:

    Run this script from Sheet 1
    This script must be assigned to a Button or run some other way.
    You did not say you wanted it run automatically.
    It looks in the first sheet in your workbook for a value in Range("C1")
    Code:
    Sub Filter_Me_Please()
    'Modified 6/26/18 10:35 PM EDT
    Application.ScreenUpdating = False
    Dim Lastrow As Long
    Dim c As Long
    Dim s As String
    c = "11" ' Column Number Modify this to your need
    s = Sheets(1).Range("C1").Value
        For i = 2 To Sheets.Count
            Lastrow = Sheets(i).Cells(Rows.Count, c).End(xlUp).Row
            With Sheets(i).Cells(1, c).Resize(Lastrow)
                .AutoFilter 1, s
                counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
                If counter > 1 Then
                Else
                    MsgBox "The Value " & s & "  Not found on Sheet  " & Sheets(i).Name
                    .AutoFilter
                End If
            End With
    Next
    Application.ScreenUpdating = True
    End Sub
    Thanks MAIS - I'm getting Run-time error '1004': though - Autofilter method of Range class failed.

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,821
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    I test all my scripts.

    It's looking for a value in Sheets(1).Range("C1")

    What do you have in this range?

    What other code do you have in these sheets?
    Last edited by My Aswer Is This; Jun 27th, 2018 at 12:14 AM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Jul 2014
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    I think it may be because I've just found there are a number of hidden sheets, some of which only have a couple of columns (it's not my workbook, I've just been told to "make it work", lol). I'll have a play around and try and fix.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,821
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    It's always best to test scripts like this on a empty workbook.
    Create a few sheets like you want and then run the script on just a few sheets.
    If it works there but not on your actual workbook you know the problem is with you workbook and not the script.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,821
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Apply Auto Filter in Sheet 1 to all other sheets

    Try this:
    Will skip over hidden sheets:
    Code:
    Sub Filter_Me_Please()
    'Modified 6/26/18 11:44 PM EDT
    Application.ScreenUpdating = False
    Dim Lastrow As Long
    Dim c As Long
    Dim s As String
    c = "11" ' Column Number Modify this to your need
    s = Sheets(1).Range("C1").Value
        For i = 2 To Sheets.Count
        If Sheets(i).Visible = True Then  ' Modified here if sheet is hidden script skips over this sheet
            Lastrow = Sheets(i).Cells(Rows.Count, c).End(xlUp).Row
            With Sheets(i).Cells(1, c).Resize(Lastrow)
                .AutoFilter 1, s
                counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
                If counter > 1 Then
                Else
                    MsgBox "The Value " & s & "  Not found on Sheet  " & Sheets(i).Name
                    .AutoFilter
                End If
            End With
            End If
    Next
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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