VBA insert Week number and Date

[Samhill62]

Hi All,

I have the following code to generate a Week number in column A and a date in column B every time a cell is changed in column C of my spreadsheet. Unfortunately it does not work for multiple entries. For example, If I copy and paste a group of eight entries, it only updates the first row. How can I update the code to allow multiple entries to be updated at once?

Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the edited cell is in column C and not the header row
If Not Intersect(Target, Range("C2:C200")) Is Nothing And Target.Row > 1 Then
' Add today's date to column B for the same row as the edited cell
Cells(Target.Row, "B").Value = Date
' Add the week number to column A for the same row as the edited cell
Cells(Target.Row, "A").Value = WorksheetFunction.WeekNum(Date, vbMonday) - 1
End If
End Sub

Thanks in advance.

 

[Samhill62]

Many thanks again Jimmypop. This works a treat also.

 

[Jimmypop]

Did it work they way you wanted....

UPDATE: Glad we could assist...