Vfrdtyky

I use the following VBA script:

    Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xCount As Long

    Dim valueCell As Range

    Dim timeStampCell As Range

    Dim targetCell As Range

    Dim xVal As Long



    Application.EnableEvents = False



    '''''EDIT''''''



    Set targetCell = Range("G15")

    Set timeStampCell = Range("R2")

    Set valueCell = timeStampCell.Offset(0, 1)



    '''''''''''''''



    xCount = Cells(ActiveSheet.Rows.Count, valueCell.Column).End(xlUp).Row - 1



    If Target.Address = targetCell.Address Then

        valueCell.Offset(xCount, 0).Value = targetCell.Value

        timeStampCell.Offset(xCount, 0).Value = Date

    Else

        If valueCell.Offset(Cells(ActiveSheet.Rows.Count, valueCell.Column).End(xlUp).Row - 2, 0).Value <> targetCell.Value Then

        valueCell.Offset(xCount, 0).Value = targetCell.Value

        timeStampCell.Offset(xCount, 0).Value = Date

        End If

    End If



    Application.EnableEvents = True



End Sub

It works properly when I use it in the active sheet (Data).
Actually, the script checks if there is a change in the target value (the value is updated using formulas from other cells) and if the target value (G15) has been changed, then copy the new value in the first available row of the table.

The problem is that I need to move the table to another sheet.
I used a formula which copies the value from G15 of the initial sheet to the cell E1 of the new sheet (MonthlyData) and I moved the VBA script from "Data" to "MonthlyData". I have modified the script as follows:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xCount As Long

    Dim valueCell As Range

    Dim timeStampCell As Range

    Dim targetCell As Range

    Dim xVal As Long



    Application.EnableEvents = False



    '''''EDIT''''''



    Set targetCell = Sheets("MonthlyData").Range("E1")

    Set timeStampCell = Sheets("MonthlyData").Range("A2")

    Set valueCell = timeStampCell.Offset(0, 1)



    '''''''''''''''



    xCount = Cells(Sheets("MonthlyData").Rows.Count, valueCell.Column).End(xlUp).Row - 1



    If Target.Address = targetCell.Address Then

        valueCell.Offset(xCount, 0).Value = targetCell.Value

        timeStampCell.Offset(xCount, 0).Value = Date

    Else

        If valueCell.Offset(Cells(Sheets("MonthlyData").Rows.Count, valueCell.Column).End(xlUp).Row - 2, 0).Value <> targetCell.Value Then

        valueCell.Offset(xCount, 0).Value = targetCell.Value

        timeStampCell.Offset(xCount, 0).Value = Date

        End If

    End If

    Application.EnableEvents = True



End Sub

And now the problem... If I manually update the E1 cell to another value works perfect, if the value E1 change automatically changing a value from sheet (Data) the value E1 in MonthlyData being updated but the VBA script does not work and the value is not updated in the table...

Can anyone help?

Thanks

I think you need to add code Worksheet_Change event on Data sheet as well. I am using this as an example if Sheets("MonthlyData").Range("E1") is a formula that came from Sheets("Data").Range("A1")
Please add this code to Data sheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)



    If Target.Range("A1") Then

        Sheets("MonthlyData").Range("E1").Formula = Sheets("MonthlyData").Range("E1").Formula

    End If



End Sub

Hope this help.

Thanks.

Worksheet_Change event in MonthlyData sheet supposed not to fire as there is no change in that sheet. Only value of the cell is being changed.

It is better to wrap up the code for Worksheet_Changeof "MonthlyData" in a sub procedure and call it from DATA sheet's Worksheet_Change event from within the if branch where G10 is found changed.

Or

Simply change MonthlyData Sheet's E1 Cell directly from with in Worksheet_Change event of DATA sheet by adding one line

Sheets("MonthlyData").Range("E1").Value= targetCell.Value

EDIT:I tried the firing of Sheet("MonthlyData") Worksheet_Change event with the following code placed in Sheet("DATA") Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Sheets("DATA").Range("G10") Then

Sheets("MonthlyData").Range("E1").Value = Sheets("DATA").Range("G10").Value

End If

End Sub

It is successfully putting G10 (I used G10 instead of G15) value to E1 and firing of Sheet("MonthlyData") Worksheet_Change event. it is again checked with your full code of MonthlyData Worksheet_Change event and found working.