Referring worksheet in a VBA module

[csaraja]

Hi,

Need your support in below query...

I have a workbook with template XLS sheet, which has to be dynamically copied as separate XLS sheet bearing the name "Week 21", "Week 22" and so on, for every week inside the workbook. If I write the code inside the worksheet "Template" and copy it and create a new sheet like "Week 22", the macro also copy the worksheet code to the new sheet ==> making the workbook size increased for every sheet addition.

To avoid that I need to place the vba code in VB Module. The code I wrote is

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IntersectRange As Range
Dim WatchRange As Range
Static JustOpened As Boolean
Application.EnableEvents = False
Application.DisplayAlerts = False
' set the range for testing the change of column data to update the Last Updated On column automatically
Set WatchRange = Range("C8:P" & LastRow)
If JustOpened = False Then
JustOpened = True
Else
Set IntersectRange = Intersect(Target, WatchRange)
If IntersectRange Is Nothing Then
'Do Nothing Spectacular
Else
' Check if there was data in C column on the row
' check with there was a change in H, I and J Column
If Target.Row > 7 And Target.Value <> "" Then
If Target.Column > 7 And Target.Column < 11 Then
If Not IsDate(Target.Value) Then MsgBox "Please enter a valid date format (dd-mm-yyyy hh:mm AM/PM)", , Application.Name
Select Case Target.Column
Case 8
Range("H" & Target.Row).Value = Format(Range("H" & Target.Row).Value, "dd-mm-yyyy hh:mm AM/PM")
Case 9
Range("I" & Target.Row).Value = Format(Range("I" & Target.Row).Value, "dd-mm-yyyy hh:mm AM/PM")
Case 10
Range("J" & Target.Row).Value = Format(Range("J" & Target.Row).Value, "dd-mm-yyyy hh:mm AM/PM")
End Select
If Range("C" & Target.Row).Value <> "" Then
If Range("R" & Target.Row).Value = "" Then
' R contains Row Creation Date
Range("R" & Target.Row).Value = Format(Now, "dd-mm-yyyy hh:mm AM/PM")
Else
' S contains Row last update date
Range("S" & Target.Row).Value = Format(Now, "dd-mm-yyyy hh:mm AM/PM")
End If
End If
End If
' since data starts from 8th Row
Range("B" & Target.Row).Value = Target.Row - 7
Range("Q" & Target.Row).Value = UserNameWindows()
' color which are system generated
Range("B" & Target.Row).Interior.Color = RGB(190, 190, 190)
Range("Q" & Target.Row).Interior.Color = RGB(190, 190, 190)
Range("R" & Target.Row).Interior.Color = RGB(190, 190, 190)
Range("S" & Target.Row).Interior.Color = RGB(190, 190, 190)
End If
End If
End If
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Would appreciate, how to modify the code so that it work well for my need.

Thanks
Raja CSA

 

[Jerry Sullivan]

Hi Raja and Welcome to the Board,

If the Worksheet_Change Code should be applied to every worksheet in the workbook, then instead of placing that code in each worksheet, you can use a Workbook_SheetChange event instead.

The code must be placed in the ThisWorkbook module and the declaration looks like this...

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

End Sub

If the full code is not to be executed on every sheet, you could test for the sheet's name when the code is triggered.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Left(Sh.Name, 4) <> "Week" Then Exit Sub

    
    
    '---code continues for sheets beginning with Week....


    
End Sub