Nsryjdtyk

I have a worksheet within a multi-worksheet workbook Workbook1 which contains some VBA code that executes when certain calculated cells change in value. All works well. However, if that workbook is active and I open another workbook Workbook2 unrelated to Workbook1, it seems the VBA code for Workbook1 attempts to execute and I get a message box with Runtime Error:

"9: Subscript out of range"

message. As If the code for Workbook1 is being executed. If I press the DEBUG button on the runtime error, I see the code for workbook1.

I need to limit the execution of the specific VBA code to Workbook1, FEED_ANALYSIS Sheet.

Included is the Workbook1 code which is attached to the FEED_ANALYSIS sheet.

Thank you in advance.

Private Sub Worksheet_Calculate()

    Dim Cost_Per_day

    Dim COST_kg

    Dim COST_GROSS_kg

    Dim AVG_SALES_PRICE

    Dim COST_NET_PURCHASE

    Dim PROFIT_GROSS

    Dim PROFIT_NET

    Dim PROFIT_NET_X

    Dim Flag_set



    Dim dtmTime As Date

    Dim Rw As Long



'If Critical Cells change, move contents to Log sheet



    Dim Xrg As Range

    Set Xrg = Range("E5:I11")

    If Not Intersect(Xrg, Range("E5:I11 ")) Is Nothing Then

        dtmTime = Now()

        Cost_Per_day = Worksheets("FEED_ANALYSIS").Range("E7").Value

        COST_kg = Worksheets("FEED_ANALYSIS").Range("F7").Value

        COST_GROSS_kg = Worksheets("FEED_ANALYSIS").Range("G7").Value

        AVG_SALES_PRICE = Worksheets("FEED_ANALYSIS").Range("I5").Value

        COST_NET_PURCHASE = Worksheets("FEED_ANALYSIS").Range("G11").Value

        PROFIT_GROSS = Worksheets("FEED_ANALYSIS").Range("I7").Value

        PROFIT_NET = Worksheets("FEED_ANALYSIS").Range("I8").Value

        PROFIT_NET_X = Worksheets("FEED_ANALYSIS").Range("I9").Value



        Rw = Sheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1



        With Sheets("LOG")

            datcomp = .Cells(Rw - 1, 1)



       ' if the previous entry date is the same as the current date, do not create the entries... one entry per day



            If Year(datcomp) = Year(dtmTime) And Month(datcomp) = Month(dtmTime) And Day(datcomp) = Day(dtmTime) Then GoTo NoUpd



            .Cells(Rw, 1) = dtmTime

            .Cells(Rw, 2) = Cost_Per_day

            .Cells(Rw, 3) = COST_kg

            .Cells(Rw, 4) = COST_GROSS_kg

            .Cells(Rw, 5) = AVG_SALES_PRICE

            .Cells(Rw, 6) = COST_NET_PURCHASE

            .Cells(Rw, 7) = PROFIT_GROSS

            .Cells(Rw, 8) = PROFIT_NET

            .Cells(Rw, 9) = PROFIT_NET_X

            .Cells(Rw, 11) = .Cells(Rw - 1, 1)

NoUpd:

        End With   

    End If

End Sub

When starting the macros the first time, have it get the workbook name in a global variable. Then use that variable in qualifying the wokbook tou want to work in. If you now open another workbook, the macro will work on the correct wokbook:

Dim gWorkBookName As String



Private Sub Worksheet_Calculate()

    '...

    If (gWorkBookName = "") Then

        gWorkBookName= ActiveWorkbook.Name

    End If

    '...

    With Workbooks(gWorkBookName)

        Cost_Per_day = .Worksheets("FEED_ANALYSIS").Range("E7").Value

        '...

    End With

    '...

End Sub