Excel - Apply VBA code to multiple sheets

December 2016




Issue


I have salary Excel file. This file includes 200 sheets which are for each employee.
I have the following VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)   
If Target.Address <> "$C$9" Then Exit Sub   
If Target = "seconded" Then   
Range("a12").EntireRow.Hidden = True   
Range("a14").EntireRow.Hidden = True   
Else   
Range("a12").EntireRow.Hidden = False   
Range("a14").EntireRow.Hidden = False   
End If   
End Sub 


I need to put/write this code once only to work for all 200 sheets otherwise I have to copy and paste it in each of 200 sheets.

Solution


It seems to me that you don't want to apply the code to a single sheet but to the entire workbook.
Therefore double-click on ThisWorkbook below the sheets in VB and paste your code like:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)  
If Target.Address <> "$C$9" Then Exit Sub  
If Target = "seconded" Then  
Range("a12").EntireRow.Hidden = True  
Range("a14").EntireRow.Hidden = True  
Else  
Range("a12").EntireRow.Hidden = False  
Range("a14").EntireRow.Hidden = False  
End If  

End Sub  


Thanks to TrowaD for this tip .

Related :

This document entitled « Excel - Apply VBA code to multiple sheets » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.