Macro Code Help for multiple worksheets.

[Gajendran Yadhav]

I have multiple sheets in a workbook. I have custom named all those. I want a specific macro to apply only to few of the custom named worksheets...
When i place the below code to each worksheet macro, this works very well. (auto clears the contents in the adjacent cells ( O & Q) then and there when any change is made in column P)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range 
   Dim cell As Range  
  ' Set the range to the entire 16th column (Column P) 
   Set rng = Intersect(Target, Columns(16))   
 ' Check if there are any changes in Column P    
If Not rng Is Nothing Then    
    Application.EnableEvents = False ' Disable events to prevent infinite loop     

   ' Loop through each changed cell in Column P      
  For Each cell In rng           
 ' Clear the corresponding cells in Columns O (15th) and Q (17th)     
       cell.Offset(0, -1).ClearContents ' Clear contents of Column O (15th column)         
   cell.Offset(0, 1).ClearContents ' Clear contents of Column Q (17th column)      
  Next cell      

  Application.EnableEvents = True ' Enable events after the changes are made
    End If
End Sub

Since i need this to happen in almost 24 worksheets, i place it in all 24 worksheet macros. this makes the file size huge.

is there any possibility to have this code in a workbook module so that it does the work as intended...
when i tried it, the code seems not working. the modified code i wrote is..

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    ' Check if the changed worksheet is one of the custom-named worksheets
    Select Case Sh.Name
        Case "AP", "AS", "BH", "BR", "CG", "Corp", "GJ", "JH", "KA", "KA2", _
             "KL", "MH", "MP", "NB", "OD", "PB", "RJ", "SB", "TN", "TN2", _
             "TS", "UK", "UP1", "UP2"

            ' Set the range to the entire 16th column (Column P)
            Set rng = Intersect(Target, Sh.Columns(16))

            ' Check if there are any changes in Column P
            If Not rng Is Nothing Then
                Application.EnableEvents = False ' Disable events to prevent infinite loop
                
                ' Loop through each changed cell in Column P
                For Each cell In rng
                    ' Clear the corresponding cells in Columns O (15th) and Q (17th)
                    cell.Offset(0, -1).ClearContents ' Clear contents of Column O (15th column)
                    cell.Offset(0, 1).ClearContents ' Clear contents of Column Q (17th column)
                Next cell
                
                Application.EnableEvents = True ' Enable events after the changes are made
            End If
    End Select
End Sub

when i manually tried running the macro, a dialog box opens, asking for the Macro Name:
the dialog box is

any help will be appreciated...

TIA.

 

[Georgiboy]

When you place this code into the 'ThisWorkbook' module you don't need to run it. Make sure you are in a sheet that is named in your case statement (i'e. a sheet named 'AP') and then make a change to a value in column P and it will run by itself in the same way it did at sheet level.

You don't need that line 'Dim ws As Worksheet'

If it still won't run then maybe you have EnableEvents stuck on false, if this is the case try running the below in a standard module and then try again:

Sub test()
    Application.EnableEvents = True
End Sub