Pulling dynamic data from Sheet2 into Sheet1 then to Sheet3..10

Question

So I have Sheet1 and Sheet2, Sheet1 is user set settings and Sheet2 is a table of values generated by different values in Sheet1. Now Sheet3..10 can be created, which will pull a value from Sheet2 based on calculations on it.

The problem I'm running in to is when say Sheet3!H20 is updated from Sheet2!I15, how do I get Sheet3!H20 to have =VALUE instead of =Sheet2!I15 so that Sheet4..10 can reuse this table?

I have tried Workbook_SheetCalculate(ByVal Sh As Object)

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Select Case Sh.Name
        Case "Sheet1"
        Case "Sheet2"
        Case Else
            ThisWorkbook.Worksheets("Sheet2").Range("$C$14").Value = "=" & Sh.Range("$D$23").Value

            Sh.Range("$H$20").Value = "=" & ThisWorkbook.Worksheets("Sheet2").Range("$I$15").Value
    End Select
End Sub

But when I do this, it just recalculates constantly and eventually Excel crashes. I have also tried going in to each worksheet with Worksheet_Calculate and when I do that I get a runtime error for Range is not in the Worksheet object.

And ideas?

Answer 1

try putting this code at the start of your macro...

Application.Calculation = xlManual

then at the end put

Application.Calculation = xlAutomatic

To ensure that all of the calculations are performed without invoking an endless loop, then try something like this.

1.Declare a global variable called IsCalculating
2.Wrap your code in an if statement

if not IsCalculating then
    IsCalculating = true
    Application.Calculation = xlManual

    `put your code here`

    Application.Calculation = xlAutomatic
    Application.Calculate
    IsCalculating = false
end if

Answer 2

I will try the IsCalculating variable. To get it to work for the night, I came up with this..

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case "Settings"
        Case "Year To Date"
        Case "Federal Table"
            Select Case ActiveSheet.Name
                Case "Settings"
                Case "Year To Date"
                Case "Federal Table"
                Case "State Table"
                Case "CO Table"
                Case "IA Table"
                Case Else
                    ActiveSheet.Range("$H$20") = "=" & Worksheets("Federal Table").Range("$I$15").Value
            End Select
        Case "State Table"
            Select Case ActiveSheet.Name
                Case "Settings"
                Case "Year To Date"
                Case "Federal Table"
                Case "State Table"
                Case "CO Table"
                Case "IA Table"
                Case Else
                    ActiveSheet.Range("$H$21") = "=" & Worksheets("State Table").Range("$H$6").Value
            End Select
        Case "CO Table"
        Case "IA Table"
        Case Else
            If Worksheets("Federal Table").Range("$C$14").Value <> Sh.Range("$D$23").Value Then
                Worksheets("Federal Table").Range("$C$14").Value = "=" & Sh.Range("$D$23").Value
                Worksheets("State Table").Range("$H$2").Value = "=" & Sh.Range("$D$23").Value
            End If
    End Select
End Sub

But it just looks sloppy to me.