Apply Auto Filter in Sheet 1 to all other sheets

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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!
 
Upvote 0
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 ??
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top