Combobox ActiveX/ - Filter worksheets

Darms

New Member
Joined
May 14, 2010
Messages
10
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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!!!!!
 
Upvote 0

Forum statistics

Threads
1,223,428
Messages
6,172,042
Members
452,444
Latest member
ShaImran193

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