Reload data on worksheet change

Question

I have a Excel workbook with two worksheet (sheet1 and sheet2). I've some cell of sheet1 referenced from sheet2. I've done this using formula like this...

=sheet1!C2

This should change the derived cells of sheet2 if I change values in sheet1. but unfortunately it's working at first load but not after that.. how can i refresh the derived cells using macro

by default it should update automatically. are there any macros running? set Application.ScreenUpdating = True and Application.Calculation = xlCalculationAutomatic — user2140173

Ripster Ripster · Accepted Answer · 2013-06-17T12:58:47

Add this code to Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Range

    For Each col In Sheet2.UsedRange
        col.Calculate
    Next
End Sub

Every time you make a change on sheet1, sheet2 will recalculate which should update the value of formulas.

Reload data on worksheet change

1 Answers