Previous Topic Next Topic

VBA code in excel for 200 sheets

Solved/Closed
issa - Jan 30, 2012 at 11:55 PM
 Issa - Jan 31, 2012 at 11:24 AM
Dear all who can help me.

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.

Thank you in advance for any kind assistant.

Best regards,
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jan 31, 2012 at 09:53 AM
Hi Issa,

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

Best regards,
Trowa
Answer 2 / 2
Issa
Jan 31, 2012 at 11:24 AM
Dear Trowa ,

Thank a lot to all your assistant and appreciated a lot your prompt reply as well.

Best rgards,
Issa

Similar discussions

lava hard reset & factory reset
AVAY -
Har -

3 responses