Making a worksheet macro work for whole workbook

**john_london** · 01-27-2011, 12:15 PM

Hi Guys -

I have written a small macro in the worksheet code which just hides some rows in the spreadsheet depending on what's selected in cell B10. But I've just realised that there can be many worksheets in the workbook, and don't want to copy and paste this again for each one. Could i move this code to the workbook and how would I change it?

Thanks!!

John

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("B10") = "" Then
    Rows("14:64").EntireRow.Hidden = False
    End If
    If Range("B10") = "TBD" Then
    Rows("14:64").EntireRow.Hidden = False
    End If
    If Range("B10") = "Full" Then
    Rows("14:64").EntireRow.Hidden = False
    End If
    If Range("B10") = "PI only" Then
    Rows("17:17").EntireRow.Hidden = True
    Rows("19:19").EntireRow.Hidden = True
    Rows("28:28").EntireRow.Hidden = True
    Rows("31:34").EntireRow.Hidden = True
    Rows("36:37").EntireRow.Hidden = True
    End If
  
End Sub

**Domski** · 01-27-2011, 12:27 PM

Pop this on the ThisWorkbook code page:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
With Sh
   
    If .Range("B10") = "" Then
    .Rows("14:64").EntireRow.Hidden = False
    End If
    If .Range("B10") = "TBD" Then
    .Rows("14:64").EntireRow.Hidden = False
    End If
    If .Range("B10") = "Full" Then
    .Rows("14:64").EntireRow.Hidden = False
    End If
    If .Range("B10") = "PI only" Then
    .Rows("17:17").EntireRow.Hidden = True
    .Rows("19:19").EntireRow.Hidden = True
    .Rows("28:28").EntireRow.Hidden = True
    .Rows("31:34").EntireRow.Hidden = True
    .Rows("36:37").EntireRow.Hidden = True
    End If
    
End With

End Sub

Dom