Is it possible to easily run a single optimization problem that spans many worksheets of the same workbook...if yes, how?
Yes, with any of our advanced Solvers, you can do this, and it should "just work" -- many of our users build models in this form. Each worksheet can define an optimization model; it is the location of the objective cell that determines which model is associated with a worksheet. The decision variables and the constraints can be references to cells on other worksheets. There is a modest performance "hit" when you spread a model across multiple worksheets, but this is usually not enough to worry about. Sometimes users build worksheets that include elements of their optimization model, and tens of thousands of other formulas whose values are unaffected by the decision variables -- this can slow things down and is not recommended, but it is permitted.
With the basic Solver that comes with Excel, I'm a little less sure, but I believe you would need to put all of the decision variables ("changing cells") on the "home" worksheet that includes the objective cell. Constraint and objective formulas can refer to cells on other worksheets, but the model ultimately depends on the decision variables, whose values are adjusted during the optimization.