Step two: Calling a common module from each of the worksheets.
You already have a start because you have a Worksheet_Change subroutine written in one of your worksheets:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub
I'll show you how to change that around so this subroutine calls another one. Here's a subroutine that I named DoGoalSeek with all those statements copied into it almost unchanged:
Code:
Sub DoGoalSeek(Target)
Dim r As Range
Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub
This code I write into the new module you just created. Now it can be called by any other subroutine in your workbook, like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
DoGoalSeek Target
End Sub
Here's what happens: When you change any cell in a worksheet, Excel runs the Worksheet_Change subroutine for that worksheet (if any). The only thing that subroutine does, as I've modified it above, is call DoGoalSeek and pass it the Target object (which is a pointer to the cell that was changed).
DoGoalSeek takes the Target object passed to it and does everything that used to be in your Worksheet_Change subroutine. When it's done, it passes control back to Worksheet_Change, which returns control back to you.
Now you can paste the same three-line subroutine into Sheet2, and into any other price sheet you have in your workbook, and each one will call the same logic every time a cell is changed. Then when you have to change the way DoGoalSeek works—and we will change it, in several ways—you can change it in just one place instead of in every worksheet.
Next post: What we have to do to DoGoalSeek to make it work for any row, not just row 2.