VBA Script

[camper1337]

I have a script that I have written. The script works well, but only on Sheet 1. If I enter data into sheets 2-5, the end result of the vba still goes to Sheet 1. I'd like to make the script work individually on each sheet. Thanks in advance. Code Below.

Private Sub Worksheet_Change(ByVal Target As Range)
   ''Do something only if the value changes in cell B9
   If Target.Address = Range("B9").Address Then
   Range("C8").Value = Range("C9")
   If Target.Address = "$B$9" Then
      ''Look at the full list below the Target title
      With ThisWorkbook.Names("Reading_Date").RefersToRange.CurrentRegion
         ''Look at the cell at the bottom of the list
         With .Offset(.Rows.Count, 0).Resize(1, 1)
             ''Enter the current time in the cell
             .Value = Format(Date, "Short Date")
             ''Enter the new value to the right of the time
             .Offset(0, 1).Value = Target.Value
             .Offset(0, 2).Value = Range("B11")
             .Offset(0, 3).Value = Range("B20")
          End With
      
      End With
   End If
   End If
End Sub

 

[Norie]

How exactly have you defined the named range 'Reading_Date'?

 

[JLGWhiz]

How exactly have you defined the named range 'Reading_Date'?

All indications from the previous posts is that it is only defined for sheet 1. I think the name manager needs some tweaking.