Results 1 to 2 of 2

Thread: VBA: Macro calculations done in seperate sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2016
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default VBA: Macro calculations done in seperate sheet

    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

    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"
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Florida, USA
    Post Thanks / Like
    25 Post(s)
    2 Thread(s)

    Default Re: VBA: Macro calculations done in seperate sheet

    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 by JLGWhiz; Sep 1st, 2019 at 07:20 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts