Filtering multiple worksheets with one click

[gc19]

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

 

[vcoolio]

Hello GC,

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

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.

 

[demps709]

Hello GC,

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

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.

Hi vcoolio,

Could you reupload your sample workbook? This sounds similar to something I've been struggling to accomplish. I have multiple sheets with different data sets but a mutual unique identifier column (SiteName). I want to be able to select 5-15 sites via the filter on the first sheet and have this filter apply to several of the other sheets.

 

[vcoolio]

Hello Demps,

The sample file is here.

If you want to play around with it, download and save it to your machine as free file sharing sites generally allow seven days only of access.
If you need any help with this, start a new thread. This thread is fairly old now and belongs to the OP.

Cheerio,
vcoolio.