VBA: Macro calculations done in seperate sheet

adamolej

New Member
Joined
Aug 3, 2016
Messages
2
Dear VBA Masters,

I'm doing my first steps in VBA and I am stuck for days now with something that might have
a simple solution.

I have built a Solver macro, that is running and attached to a button in a sheet named "Consolidated Financials". It helps me to optimize some financial parameters in a large sheet with many financial calculations.

I prepared a summary of the key numbers in a new sheet named "Dashboard". I need to copy that macro/button from "Consolidated Financials" to "Dashboard" sheet in a way, that when I click the Solver button in "Dashboard" it runs Solver in "Consolidated Financials", without jumping to that sheet.

Everything is in same workbook and all assumptions for Solver are in "Consolidated Financials" tab.

I would really appreciate your help! I seem to be in a pickle here. Thank you!

The code I'm using inside "Consolidated Financials" sheet is following:

Sub SolverMacro()
'
' SolverMacro Macro
'


'
SolverReset
SolverAdd CellRef:="$F$111", Relation:=2, FormulaText:="$G$347"
SolverAdd CellRef:="$G$332", Relation:=2, FormulaText:="$G$346"
SolverOk SetCell:="$G$334", MaxMinVal:=2, ValueOf:=0, ByChange:="$G$352,$F$189" _
, Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Looking at your code, I would say that it is located in a public module like module1 or module2. That would mean that the button you are using is a Form Controls button which has the option to assign a macro when you first put it on a sheet. If I am correct in my assumptions, then you do not need to copy anything. Just add a new button from the Form Controls tool box at Developer>Insert on the ribbon to the Dashboard sheet, then with the Attach Macro option, click on the 'SolverMacro' name and then click OK. The macro, as written, will run on the active sheet, so it should run on 'Dashboard' when the button on that sheet is clicked.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top