Welcome, this is a follow-up to an earlier video,
which presented one approach using an Excel
macro to goal seek across multiple columns.
But what if we have multiple business
scenarios across multiple worksheets?
Go to Visual Basic for Applications,
and then insert a Module.
Remember to notate your code,
as we are doing here in green.
Our macro will be called, gsMultiColsSheets
The message box prompt will warn users
the macro will run, if they choose "Yes"
or exit if they select "No".
Now, we will declare our object variables
for our goal seek macro.
The following green notations describe each object variable.
Before the macro runs, we want to disable
the following Excel properties, which will
improve the performance of the goal seek multi sheet macro.
This loop code will ensure the macro correctly
performs the same goal seek
across all worksheets in the active workbook.
We want to now stipulate the range in each
worksheet, which we want the goal seek to start from.
To confirm this. We want the macro to always
commence in the cell range of J9 to S9,
which are the Cost of Goods Sold numbers,
in order to arrive at our desired EBITDA numbers
from row 18 into row 14.
This line of code will simply copy and paste
the values into the Cost of Goods Sold range in row 9.
Whilst the NumCols variable will count the
number of columns in the J9:S9 range.
The cCell, sCell and gCell object variable
strings are the crucial elements of the goal seek macro.
cCell relates to row 9 of the Cost of Goods Sold numbers.
sCell are the current EBITDA calculated numbers in row 14.
Whilst gCell are desired EBITDA numbers, which we want the Goal Seek number to realise.
which we want the Goal Seek number to realise.
This line of code is the critical Goal Seek piece,
which must be completely accurate -
otherwise the macro won't work properly.
We are now ready to move the cursor
in the active sheet to cell A1.
Given our Goal Seek macro is finished
with the current sheet, we want the macro
to move to the next worksheet and repeat
the above Goal Seek process.
Now the macro has looped though all worksheets,
we can now re-enable those three Excel properties.
Let's review the macro,
before we test it out on our worksheets.
We are now ready to test the multi column,
multi sheet Goal Seek macro.
Our Goal Seek macro is correctly working across the sheets.