Hello,
I am trying to hide a variable number of rows in 8 different worksheets based on a name selected from a drop down menu in a different worksheet. Each time a new name is selected in the drop down all the rows need to be unhidden then re-hidden based on the new name.
In sheet 1 I have a drop down with roughly 200 names. Each time one is selected I need all the rows NOT associated with this name to become hidden in Sheets 4-12 and only show the rows that include the selected name. If possible I would then like to password protect sheets 4-12 so they cannot be edited or manipulated in any way.
Here is a synopsis of my workbook:
Sheet 1 is the dashboard and contains results that have been compiled in a different worksheet (Sheet 3)
Sheet 2 is very hidden and contains the list of names that appear in the drop down and a number associated to them (some names have multiple numbers and some only have 1)
Sheet 3 is very hidden and contains the compiled data from sheets 4-11 based on name
Sheets 4-12 have the data that is being compiled into sheet 3 and shown on sheet 1. these sheets currently have just the number associated with each name but I can add a column for the name if needed to make this work.
Sheets 4-12 can have from 10-1500 rows of data depending on the month
When presenting the data to the specific name selected in the drop down in sheet 1 I need only the data (rows) within sheets 4-12 that corresponds to this name shown and all the other rows hidden from view.
So far this is the code I have to unhide all the rows. I am unsure of how to call each case in order for the code to hide the necessary rows.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Index
Case 4, 5, 6, 7, 8, 9, 10, 11, 12
If Target.Address(False, False) = "C4" Then
Application.ScreenUpdating = False
Worksheet("Sheet4").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheet("Sheet5").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheet("Sheet6").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheet("Sheet7").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheets("Sheet8").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheets("Sheet9").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheets("Sheet10").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheets("Sheet11").Activate
Cells.Select
Rows("3:1500").Hidden = False
Worksheets("Sheet12").Activate
Cells.Select
Rows("3:1500").Hidden = False
Select Case Target.Value
Case "A", "B"
Rows("25:34").Hidden = True
Rows("44:53").Hidden = True
Case "C", "D"
Rows("24:44").Hidden = True
Case "E"
Rows("34:53").Hidden = True
End Select
Application.ScreenUpdating = True
End If
End Select
End Sub
Display More
Any help would be greatly appreciated.