0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Apply VBA code to multiple sheets




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 .
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Published by . Latest update on 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 (https://ccm.net/).

0 Comments