Link a macro to a cell value

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I have a workbook with a varying number of worksheets.

Each sheet has a "sheet total" cell that sums a range
of "quantity" cells. The first worksheet has a "project
total", which will contain the sum ot the sheet totals for
the entire workbook. I have the function for that task.

I'd like the "project total" function (macro) to run
whenever any of the "sheet total" values change.

how do I associate the macro to that event?

Thanks in advance,
Rich
 
Hi Rich,
As I understand your question your master sheet shows only one
total from the other sheets -- no individual sheet totals.
You will have to be able to find those totals; hopefully, they
are in the same location on each worksheet.

You would use a Calculate event
http://www.mvps.org/dmcritchie/excel/event.htm

You might be able to eliminate use of a macro entirely if
you can use a 3-D total see Excel Help, Answer wizard: 3-D sum
one such topic you will find is (you can use 3D or 3-D in the above search)
Refer to the same cell or range on multiple sheets by using a 3-D reference.

Things you do not mention. Adding additional worksheets, is the sum
on the same location on each sheet.

You might also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
which handles pulling off descriptions and totals from specific
locations of named worksheets. Don't think it applies to your
request but you might find it interesting for related usage.
 
Why are you using a macro for anything?

Assuming that the individual sheets have their total in cell C28, on
the summary worksheet, use =SUM(Sheet1:Sheet10!C28)

where Sheet1 and Sheet10 are the first and last worksheets of interest
to you.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
The number of worksheets will vary from workbook to
workbook.

As for "macros", perhaps I mis-use the term, but I can run
the functions from the "macro" dialog, if I desire...

Rich
 
It doesn't matter if the number of sheets vary. Just use two dummy
sheets, say SheetFirst and SheetLast. Put all other other sheets in
between these two. Your formula should be
=SUM(SheetFirst:SheetLast!C28)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

The number of worksheets will vary from workbook to
workbook.

As for "macros", perhaps I mis-use the term, but I can run
the functions from the "macro" dialog, if I desire...

Rich
Click to expand...
 
Back
Top