-1

Very new to this so. Is there any way to have the option cell K473 be on tab 1 and change the hidden rows on tabs 2 to 11? The below works perfectly on a single sheet, but would like to have the same result on several sheets, based on the choice on the first sheet.

Thanks in advance, John

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCell As Range: Set iCell = Intersect(Range("k473"), Target)
If iCell Is Nothing Then Exit Sub

If iCell.Value = "Quoted_Rates" Then
    Rows("481:492").Hidden = False
    Rows("474:480").Hidden = True
ElseIf iCell.Value = "Cost_Rate" Then
    Rows("481:492").Hidden = True
    Rows("474:480").Hidden = False
'Else ' do nothing
End If

End Sub

Improve this question
1
  • 1
    You'll probably need to add a loop. Try searching for something like "excel looping through all worksheets in a workbook" You may need to use a modifier like "(worksheet) by index"
    – gns100
    Commented Jan 10 at 18:29

1 Answer 1

Reset to default
-1

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCell As Range: Set iCell = Intersect(Range("k473"), Target)
If iCell Is Nothing Then Exit Sub

Sheets(Array("tab 1", "tab 2", "tab 3", "tab 4", "tab 5", _
    "tab 6", "tab 7", "tab 8", "tab 9", "tab 10", "tab 11")).Select
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
     ws.Rows("481:492").Hidden = iCell.Value = "Cost_Rate"
     ws.Rows("474:480").Hidden = iCell.Value = "Quoted_Rates"
Next ws
Me.Select
End Sub
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .