Combobox ActiveX/ - Filter worksheets

[Darms]

Hi there, I have tried searching MrExcel for an answer, but have not found one.

What I have is a spreadsheet with seven different different worksheets. Each worksheet contains a large list of manager names, linked to employee names, etc (columns A to H are filled with data).

What I would like to do is in Sheet1, use a Combo box to select a manager's name; and once done, I would like Sheet2 to Sheet7 to automatically filter the other worksheets with the same manager's name.

I thought this would be easy, this is something I have not done before, so I am at a loss of how to proceed.

Oh, and I am using MS Excel 2007.

 

[Darms]

Re: Combobox - Filter multiple worksheets

Update:

I did find out how to do this the following (using a macro to record the filtering of one manager):

Private Sub ComboBox1_Change()
' ManagerMacroSelect Macro
'
If ComboBox1.Value = ("Gorti,Pradeep") Then
Sheets("#1 STORMS").Activate
ActiveSheet.Range("$A$1:$J$957").AutoFilter Field:=1, Criteria1:= _
"Gorti,Pradeep"
Sheets("#2 WORKSUITE").Select
ActiveSheet.Range("$A$1:$I$5").AutoFilter Field:=1, Criteria1:= _
"Gorti,Pradeep"
Sheets("#3 PMTS").Select
ActiveSheet.Range("$A$1:$G$1098").AutoFilter Field:=1, Criteria1:= _
"Gorti,Pradeep"
Sheets("#4 FFE").Select
ActiveSheet.Range("$A$1:$J$573").AutoFilter Field:=1, Criteria1:= _
"Gorti,Pradeep"
Sheets("#5 STORMS_WORKSUITE-BATCH").Select
ActiveSheet.Range("$A$1:$H$182").AutoFilter Field:=1, Criteria1:= _
"Gorti,Pradeep"
Sheets("#1 STORMS").Select
End If
End Sub

Buuuuuutttt, I have over 200 manager names in the combo box, so I would basically have to change the manager name in the code above, then cut and paste 200 times in order for the code to correctly work.

There has to be another way!!!!!

 

[Darms]

No Help....? Really......???