Applying Worksheet_Change to lots of sheets

[crocodile]

Hello all,

I have a workbook in which there are a large number of sheets - most of which are "Person Sheets" - duplicates of the same sheet, containing data about various people. You can differentiate the "person sheets" from the other sheets as they have an x in cell B1.

Anyway, I need to add a Worksheet_Change procedure to the people sheets - if someone's sheet is changed, the "Last Update" timestamp on the sheet is updated to the current date, nothing complicated.

I know I can put the same Worksheet_Change procedure into each sheet - but this feels like a hugely laborious way of doing it. Is there a way of doing this "centrally"?

Many thanks and very best regards

Bazil

 

[Fluff]

You can use a

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

procedure in the ThisWorkbook module.
And check if the sheet name is a "Person" sheet

 

[crocodile]

Thanks Fluff,

I knew there would be a simple answer!

I've got it working (and it's still simple - so that I can understand it!):

'Whenever a change is made on one of the people sheets, the timestamp on the sheet will be updated.
'
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    If Sh.Range("B1").Value = "x" Then


        Application.EnableEvents = False
    
        '   Set the current date into the timestamp field
            Sh.Range("Q1").Value = Date
        
        Application.EnableEvents = True
    
    End If


End Sub

Many thanks for your help - that's exactly what I was looking for.

Bazil

 

[Fluff]

You're welcome & thanks for the feedback