change target range to another worksheet

[Shamz41]

Hi I have the VBA. Can i check if i want to change the target address B3 to another worksheet called Sheet1 and cell B3, how can i amend the code? The worksheet where the timestamp is on is called Report.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = vbNullString Then Exit Sub
    If Target.Address = "$B$3" Then
        Application.EnableEvents = False
        fRow = Application.Match(Target, Sheets("Table").Columns(1), 0)
        If Not IsError(fRow) Then
            With Range("A" & Rows.Count).End(xlUp)
                .Offset(1).Resize(, 4).Value = Sheets("Table").Cells(fRow, 1).Resize(, 4).Value
                .Offset(1, 4) = Now()
                .Offset(1, 5) = Environ("username")
                .Offset(1, 6) = Sheets("Table").Cells(fRow, 5)
            End With
        Else
            MsgBox "Equipment not in the list"
        End If
        Application.EnableEvents = True
    End If
End Sub

[oluapseyer]

you need to copy the entire code to Sheet1 so that the target will check the Sheet1> B3 range

[Shamz41]

Hi

When I tried that the data that was supposed to be recorded on the report tab like the username and timestamp got recorded on sheet 1.

[AlphaFrog]

Do like oluapseyer suggests; copy the code to the sheet1 code module. And then also add a reference to the Report sheet like below.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = vbNullString Then Exit Sub
    If Target.Address = "$B$3" Then
        Application.EnableEvents = False
        fRow = Application.Match(Target, Sheets("Table").Columns(1), 0)
        If Not IsError(fRow) Then
            With Sheets("Report").Range("A" & Rows.Count).End(xlUp)
                .Offset(1).Resize(, 4).Value = Sheets("Table").Cells(fRow, 1).Resize(, 4).Value
                .Offset(1, 4) = Now()
                .Offset(1, 5) = Environ("username")
                .Offset(1, 6) = Sheets("Table").Cells(fRow, 5)
            End With
        Else
            MsgBox "Equipment not in the list"
        End If
        Application.EnableEvents = True
    End If
End Sub

[Shamz41]

Hi I tried that as well and the code is not triggering. I must have missed something out. I have attached the file for your review.

[oluapseyer]

With Sheets("Report").Range("A" & Rows.Count).End(xlUp)

check this portion of code. Maybe you havent reference it to the REPORT sheet just like what youve asked.

code came from AlphaFrog.

[xlnitwit]

Try restarting Excel. The code works for me, although I think you should be matching on column 2 on the Table sheet.

[bakerman2]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = vbNullString Then Exit Sub
    If Target.Address = "$B$3" Then
        Application.EnableEvents = False
        fRow = Application.Match(Target, Sheets("Table").Columns(2), 0)
        If Not IsError(fRow) Then
            With Sheets("Report").Range("A" & Rows.Count).End(xlUp)
                .Offset(1).Resize(, 4).Value = Sheets("Table").Cells(fRow, 1).Resize(, 4).Value
                .Offset(1, 4) = Now()
                .Offset(1, 5) = Environ("username")
                .Offset(1, 6) = Sheets("Table").Cells(fRow, 5)
            End With
        Else
            MsgBox "Equipment not in the list"
        End If
        Application.EnableEvents = True
    End If
End Sub

[Shamz41]

Thank you very much!