none
Copy of changed cell value to other cell on another worksheet RRS feed

  • Question

  • Hi All

    Yes, I did try to search the web but didn't get exactly what I need.

    So...

    I have 30 sheets. 

    I wanted to automatically copy the change I made on a cell in the first sheet to the rest of the 29 sheets.

    It should copy on the same cell address on the other sheets.

    Say...

    I entered "Text" in cell A1 of Sheet "First". Cell A1 of the other sheets must have "Text" as I hit enter.

    Appreciate your help.

    Thanks

    Joy

    Monday, July 16, 2018 11:41 AM

Answers

  • Hello Notgeek,

    I would suggest you use the WorkSheetChange event to monitor changes in the first sheet. Once you changed value in the first sheet, the fire will trigger and then we could use code to catch the changed cell's address and value. And then we could use the address to get the cell in other sheets and then set value to them.

    Here is the simple code.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        Dim objCell As Range
        Dim objWorkSheet As Worksheet
        For Each objCell In Target.Cells
            CellAddress = objCell.AddressLocal
            For Each objWorkSheet In ThisWorkbook.Worksheets
            If objWorkSheet.Name <> ActiveSheet.Name Then
                objWorkSheet.Range(CellAddress).Value = objCell.Value
            End If
            Next objWorkSheet
        Next objCell
        Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Notgeek Tuesday, July 17, 2018 12:33 PM
    Tuesday, July 17, 2018 3:02 AM

All replies

  • Hello Notgeek,

    I would suggest you use the WorkSheetChange event to monitor changes in the first sheet. Once you changed value in the first sheet, the fire will trigger and then we could use code to catch the changed cell's address and value. And then we could use the address to get the cell in other sheets and then set value to them.

    Here is the simple code.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        Dim objCell As Range
        Dim objWorkSheet As Worksheet
        For Each objCell In Target.Cells
            CellAddress = objCell.AddressLocal
            For Each objWorkSheet In ThisWorkbook.Worksheets
            If objWorkSheet.Name <> ActiveSheet.Name Then
                objWorkSheet.Range(CellAddress).Value = objCell.Value
            End If
            Next objWorkSheet
        Next objCell
        Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Notgeek Tuesday, July 17, 2018 12:33 PM
    Tuesday, July 17, 2018 3:02 AM
  • Hi Joy!

    What you meant to say is a "REFERENCE FROM OTHER SHEETS".

    That's easy:

    =Sheet2!B2

    Cell B2 on Sheet2

    The value in cell B2 on Sheet2.

    Hope that helps...

    • Proposed as answer by Acindarax Tuesday, July 17, 2018 5:55 AM
    Tuesday, July 17, 2018 5:55 AM
  • Hi Terry

    Thanks for the quick response.

    That's exactly what I need!.

    Cheers!

    Tuesday, July 17, 2018 12:34 PM
  • Hi Acindarax

    Thanks for your help.

    Tuesday, July 17, 2018 12:35 PM