Same macro is not working in different sheets in excel with command button

Question

I'm trying to create a forecasting template for different channels say channel1, channel2 & channel3 Now I want to keep the same VBA code for these 3 channels (in 3 different sheet/tabs of excel), which is working fine for sheet1 but it's giving different result for sheet2 & sheet3. Have written same macro for three different sheets ( as the inputs & output would change for channels), which are connected to command buttons at these 3 sheets. The code is here under:

Sub Macro1()
' Example Solver VBA Macro
    SolverReset
           SolverOk SetCell:="$L$7", _
             MaxMinVal:=2, _
             ValueOf:="0", _
             ByChange:="$G$4:$G$6"
           SolverAdd CellRef:="$G$7", Relation:=2, FormulaText:="1"
    SolverSolve userFinish:=True
End Sub

so basically I'm trying to assign weights which would generate minimum error and the sum of weights would be 1. Now sheet1 is working fine but for sheet 2 & sheet 3 , sum of the weights are not unity (though the code is same)

The macro is working fine for first sheet but it's second condition ($G$7 =1) is not working for second and third sheet's macro.

Can someone assist me regarding this.

Answer 1

Thanks for writing Tom ...

Have tried with that ...

the code for second sheet :

Sub Macro2()

Worksheets("Sheet2").Activate

    SolverReset

           SolverOk SetCell:="$L$7", _

             MaxMinVal:=2, _

             ValueOf:="0", _

             ByChange:="$G$4:$G$6"

           SolverAdd CellRef:="$G$7", Relation:=2, FormulaText:="1"

    SolverSolve userFinish:=True

End Sub

But this also is not working ...and surprisingly only the second condition is ($G$7 =1) is not working. I'm perplexed.

Answer 2

manually activate a sheet that is not working.  After clicking the commandbutton and running the macro, then manually bring up solver and look at the various properties.   I suspect that some will still be referring to sheet1 and you will need to refine your code to specifically set references to the sheet where you are running the code 

-- 

Regards,

Tom Ogilvy