CODEWITHSUNDEEP
excelvba
Valantis Stergiopoulos
Valantis Stergiopoulos

Reputation: 422

Excel VBA - VBA script does not work on target sheet once I copy a value from another sheet

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

Upvotes: 0

Views: 123

Answers (2)

Ahmed AU
Ahmed AU

Reputation: 2777

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.

ScreenShot

Upvotes: 0

adhy wijaya
adhy wijaya

Reputation: 509

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.

Upvotes: 0

Related Questions