Excel - Apply VBA code to multiple sheets

February 2017




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


Published by aakai1056. Latest update on May 1, 2012 at 12:20 PM by aakai1056.
This document, titled "Excel - Apply VBA code to multiple sheets," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).