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]

You could try using the workbook level event SheetChange.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''Do something only if the value changes in cell B9

    If Target.Address = "$B$9" Then
        Sh.Range("C8").Value = Sh.Range("C9").Value
        ''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 = Sh.Range("B11").Value
                .Offset(0, 3).Value = Sh.Range("B20").Value
            End With

        End With
    End If

End Sub

 

[camper1337]

Now it is not posting the end result anywhere. I'm not super familiar with VB to begin with so pardon my struggle to understand.

 

[Norie]

Where did you put the code I suggested?

It should go in the ThisWorkbook module.

 

[camper1337]

I did have it in the wrong place. I moved it to the correct module and I have the same issue. If I put data in cell B9 on sheet 2, it still outputs on sheet 1

 

[Norie]

Which sheet is the named range Reading_Date on?

 

[camper1337]

All of them. That's why I'm trying to get the code to run on each sheet individually.

 

[Norie]

Perhaps this then.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''Do something only if the value changes in cell B9

    If Target.Address = "$B$9" Then
        Sh.Range("C8").Value = Sh.Range("C9").Value
        ''Look at the full list below the Target title
        With Sh.Range("Reading_Date").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 = Sh.Range("B11").Value
                .Offset(0, 3).Value = Sh.Range("B20").Value
            End With

        End With
    End If

End Sub

 

[camper1337]

Gives a runtime error of '1004' Application Defined or Object defined error. Debugging shows error on line 7

With Sh.Range("Reading_Date").CurrentRegion

 

[camper1337]

I forgot to mention that it does work on Sheet 1. Just not the other four sheets that I have.

Gives a runtime error of '1004' Application Defined or Object defined error. Debugging shows error on line 7

With Sh.Range("Reading_Date").CurrentRegion