Filtering multiple worksheets with one click

gc19

New Member
Joined
Feb 26, 2021
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
In the simplest terms possible, i am new to excel so i dont know if this is possible

I have a spreadsheet with multiple sheets. In each of these sheets there is a data set with columns and rows. They rows are all activities that need to be completed and each activity belongs to mostly one sometimes two different departments. "Department" is one of the columns in this data set. Each sheet is laid out in the same way with activities and responsible departments (there is more to the data but this is it in its simplest form). Is there a way whereby, on a separate sheet (the first sheet), i can create a link to click for each department to click on and it will filter out all subsequent sheets for activities relating to their department? If not a clickable link but a drop down menu where a department can select their department name and that would result in all subsequent sheets being filtered out by the department selected? I want each department to be able to access the same sheet and filter out their own "work" to do easily

Hopefully that makes sense and hopefully you can assist
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello GC,

I've based this on a drop down data validation list in cell A1 of a Master sheet:-

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(2).Clear

        For Each ws In Worksheets
                If ws.Name <> "Master" Then
                        With ws.[A1].CurrentRegion
                                .AutoFilter 1, Target
                                .Offset(1).EntireRow.Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
                                .AutoFilter
                        End With
                End If
        Next ws

Application.ScreenUpdating = True

End Sub

This is an event code so once a department is selected from the drop down (e.g. A,B,C,D......), each source sheet is filtered on the department column (which is Column A in the code above) and the relevant rows of data for the selected department is transferred to the Master sheet. Nothing is deleted from the source sheets but the Master sheet is cleared with each transfer of data so that only the latest data is displayed.

I don't know how your workbook is set out so I've attached a small sample workbook to show you how this could work. Just select a department from the drop down in A1 of the Master sheet.
The code is in the Master sheet module so right click on the Master sheet tab and select "View Code" from the menu that appears to see the code in place.

See here for the sample.

I hope that this helps.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,717
Messages
5,637,947
Members
416,994
Latest member
cappiccolo

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
Top